This article is half-done without your Comment! *** Please share your thoughts via Comment ***
In this post, I am building a script to execute Update Statistics (sp_updatestats) for all SQL Server Databases.
DBA set auto update statistics to ON, but then also some times DBA has to execute sp_updatestats for updating the Database Statistics.
When high volumes of data processing, Auto update option skips updating the database statistics so DBA has to update database statistics in a maintenance window.
DBA can use below script to execute sp_updatestats for all databases:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
DECLARE @SQL VARCHAR(1000) DECLARE @DBName sysname DECLARE DBcur CURSOR FORWARD_ONLY STATIC FOR SELECT [name] FROM master..sysdatabases WHERE [name] NOT IN ('master','model', 'tempdb') ORDER BY [name] OPEN DBcur FETCH NEXT FROM DBcur INTO @DBName WHILE @@FETCH_STATUS = 0 BEGIN SELECT @SQL = 'USE [' + @DBName +']' + CHAR(13) + 'EXEC sp_updatestats' + CHAR(13) PRINT @SQL FETCH NEXT FROM DBcur INTO @DBName END CLOSE DBcur DEALLOCATE DBcur |
Leave a Reply