Script – Update all statistics

Below is a script you can use to update all statistics with a full scan in a SQL Server database. This comes in super-handy for Azure SQL Databases and SQL Server Express Editions, as we don’t support Maintenance Plans with both.

SET NOCOUNT ON
GO

DECLARE updatestats CURSOR
FOR
SELECT SysSche.Name, SysObj.Name
FROM         Sys.Objects SysObj
INNER JOIN         sys.schemas SysSche ON SysObj.Schema_ID = SysSche.Schema_ID
WHERE         TYPE = 'U'              
	AND object_id NOT IN (
		SELECT object_id
		FROM sys.external_tables
		)

OPEN updatestats  

DECLARE @tableSchema NVARCHAR(128)
DECLARE @tableName NVARCHAR(128)
DECLARE @Statement NVARCHAR(300)  

FETCH NEXT
FROM updatestats
INTO @tableSchema, @tableName  

WHILE (@@FETCH_STATUS = 0)
BEGIN
	  

	SET @Statement = 'UPDATE STATISTICS '   + '[' + @tableSchema + ']' + '.' + '[' + @tableName + ']' + ' WITH FULLSCAN'
	
	PRINT @Statement -- comment this print statement to prevent it from printing whenever you are ready to execute the command below.
	--EXEC sp_executesql @Statement -- remove the comment on the beginning of this line to run the commands 
	
	FETCH NEXT
	FROM updatestats
	INTO @tableSchema, @tableName
END  

CLOSE updatestats

DEALLOCATE updatestats
GO

SET NOCOUNT OFF
GO

Alternatively, if you wish to update only out-of-date statistics then consider implementing MSSQL Tiger Team’s AdaptiveIndexDefrag solution to perform an intelligent defrag on one or more indexes and statistics for one or more databases. This solution is not yet supported with Azure SQL Database.

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *

SQL Shots with Dapinder Arora | SQL Server Solutions