SQL Tips #11 - Rebuilding the Query Cache and Statistics

This script updates the statistics for every table in the current database, forcing SQL Server to rebuild its query plans with fresh information.

The Query

EXEC sp_updatestats
SET NOCOUNT  ON
DECLARE  @SQLcommand NVARCHAR(512), @Table      SYSNAME
DECLARE curAllTables CURSOR  FOR
SELECT table_schema + '.' + table_name
	FROM   information_schema.tables
	WHERE  TABLE_TYPE = 'BASE TABLE'
OPEN curAllTables
FETCH NEXT FROM curAllTables INTO @Table
WHILE (@@FETCH_STATUS = 0)
  BEGIN
    PRINT N'UPDATING STATISTICS FOR TABLE: ' + @Table
    SET @SQLcommand = 'UPDATE STATISTICS ' + @Table + ' WITH FULLSCAN'
    EXEC sp_executesql @SQLcommand
    FETCH NEXT FROM curAllTables
    INTO @Table
  END
CLOSE curAllTables
DEALLOCATE curAllTables
SET NOCOUNT  OFF
GO

Disclaimer

These queries are provided as a guide, and are by no means perfect. I didn't write all of these - many are collated from MSDN documentation and community sources over the years.

Tags