SQL Server Performance Forum – Threads Archive

updatestats

Does anyone have any scripts to run update stats on all databases on a server?

---

I don’t have a script handy for this, but have you tried using the Maintenance Wizard? It can create a job to do exactly this. —————————–
Brad M. McGehee, MVP
Webmaster
SQL-Server-Performance.Com

---

or you can write a simple select against the sysobjects table to generate your own. Here is a sample
DECLARE @strSPName VARCHAR(100) DECLARE CUR_SP_Name CURSOR FAST_FORWARD FOR
SELECT [name] FROM sysobjects (nolock) WHERE xtype = ‘u’ AND status >= 0 ORDER BY [name] OPEN CUR_SP_Name FETCH NEXT FROM CUR_SP_Name INTO @strSPName WHILE @@FETCH_STATUS = 0
BEGIN
EXEC update statistics @strSPName
FETCH NEXT FROM CUR_SP_Name INTO @strSPName
END CLOSE CUR_SP_Name
DEALLOCATE CUR_SP_Name
GO

---

Thanks, Is there a way to script out a maintenance plan?

---

You can set up a job to have this run in each database. Save the results of the job to a table and you will also have an audit log of things each night. CREATE PROCEDURE sp_system_admin_maintenance –Name:sp_system_admin_maintenance
–Purpose: This stored Procedure(SP) will be used for running nightly maintenance on
–all databases. It resides in the master database of each database server.
–1)UPDATE STATISTICS on all tables.
–2)UPDATE USAGE on the database.
–3)DBCC CHECKDB on the database.
—
–Format:EXEC sp_system_admin_maintenance
—
–Example:EXEC sp_system_admin_maintenance
—
–Action:Authorate:Comments:
———————- –/–/——————————————–
–CreatedDerrick Leggett11/11/2003 Initial Development
–ModifiedDerrick Leggett02/17/2004Added shrinkdatabase logic into maintenance.
— AS SET ARITHABORT ON –Record start time
PRINT ‘START TIME: ‘ + CAST(GETDATE() AS VARCHAR(255)) –Declare needed variables.
DECLARE
@int_error_counter INT,
@int_max INT,
@int_counter INT,
@txt_sql NVARCHAR(4000),
@txt_name NVARCHAR(4000),
@txt_owner NVARCHAR(255),
@txt_db NVARCHAR(255) –Declare table variable to hold table or procedure names.
DECLARE @tbl_names TABLE (
int_id INT IDENTITY(1,1) PRIMARY KEY,
txt_name VARCHAR(255),
txt_owner VARCHAR(255)) –Insert into table all user tables.
INSERT @tbl_names(
txt_name,
txt_owner) SELECT
so.name,
su.name
FROM
sysobjects so
INNER JOIN sysusers su ON so.uid = su.uid
WHERE
so.xtype = ‘U’
AND so.name NOT LIKE ‘dt%’ –Set up loop to run DBCC commands against all user tables.
SELECT
@int_max = (SELECT MAX(int_id) FROM @tbl_names),
@int_counter = (SELECT MIN(int_id) FROM @tbl_names) WHILE @int_counter <= @int_max
BEGIN SELECT @txt_name = (
SELECT ‘[‘ + txt_owner + ‘].[‘ + txt_name + ‘]’
FROM @tbl_names
WHERE int_id = @int_counter) —-Reindex all user tables.
–SELECT @txt_sql = ‘DBCC DBREINDEX(”’ + @txt_name + ”’)’
—
–PRINT @txt_sql
–EXEC ( @txt_sql ) –Update the statistics on all user tables.
SELECT @txt_sql = ‘UPDATE STATISTICS ‘ + @txt_name PRINT @txt_sql
EXEC ( @txt_sql ) SELECT @int_counter = @int_counter + 1
END SELECT @txt_db = DB_NAME() –Update the usage on the database.
PRINT ‘DBCC UPDATEUSAGE (‘ + @txt_db + ‘) WITH NO_INFOMSGS’
DBCC UPDATEUSAGE (@txt_db) WITH NO_INFOMSGS –Update the usage on the database.
PRINT ‘DBCC CHECKDB (‘ + @txt_db + ‘) WITH NO_INFOMSGS’
DBCC CHECKDB (@txt_db) WITH NO_INFOMSGS
–Record end time.
PRINT ‘END TIME: ‘ + CAST(GETDATE() AS VARCHAR(255)) GO
Derrick Leggett

---

Thanks I will add these to my bag of tricks.

---

Chris I modified the code so that it will run on each database. DECLARE @strDBName VARCHAR(100) DECLARE CUR_SP_Name CURSOR FAST_FORWARD FOR
SELECT [name] FROM sysdatabases (nolock) ORDER BY [name] OPEN CUR_SP_Name FETCH NEXT FROM CUR_SP_Name INTO @strDBName WHILE @@FETCH_STATUS = 0
BEGIN
EXECUTE (‘USE ‘[email protected]+’ EXEC sp_updatestats’)
FETCH NEXT FROM CUR_SP_Name INTO @strDBName
END
CLOSE CUR_SP_Name
DEALLOCATE CUR_SP_Name
GO

---

]]>