Update statistics for multiple databases

Updating statistics can be a repeated task , required to be run across your entire environment.

So, the above script can update the statistics of all databases (excluding system databases). As a hint, it would be best to set up a SQL Agent Job.

Here is a SQL script to run


DECLARE @SQL VARCHAR(1000)
DECLARE @DB sysname

DECLARE curDB CURSOR FORWARD_ONLY STATIC FOR
SELECT [name]
FROM master..sysdatabases
WHERE [name] NOT IN ('model', 'tempdb')
ORDER BY [name]

OPEN curDB
FETCH NEXT FROM curDB INTO @DB
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @SQL = 'USE [' + @DB +']' + CHAR(13) + 'EXEC sp_updatestats' + CHAR(13)
PRINT @SQL
FETCH NEXT FROM curDB INTO @DB
END

CLOSE curDB
DEALLOCATE curDB

Subscribe

0 comments