LearnItFirst User Forum

/*
    Author: Scott Whigham from http://www.LearnSqlServer.com/

    Description: This script resets all stats to use the Default sampling rate/amount

    Misc Notes: I had a client who's previous DBA had two jobs that ran one after the other every night:
        Step 1: DBCC DBREINDEX all tables
        Step 2: UPDATE STATISTICS WITH FULLSCAN, NORECOMPUTE for all tables

        In case you don't know, DBCC DBREINDEX includes rebuilding the stats making Step 2 unnecessary but, because
            it was there, SQL Server ran it anyway. I needed a script to reset all table stats (including columns and indexes)
            to the default sampling rate so that I could then run sp_updatestats on each table.
   
    Versions: SQL Server 2000, 7.0
   
    Creation Date: September 20, 2006

    For more scripts like this one, visit http://forums.learnsqlserver.com/codesamples.aspx
*/
SET NOCOUNT ON

DECLARE @Table TABLE (Id INT NOT NULL IDENTITY(1,1), TableName SYSNAME, SchemaName SYSNAME)
DECLARE @CurrentId INT, @Counter INT, @DynamicSql NVARCHAR(1024), @TableName SYSNAME
    , @StartTime DATETIME, @EndTime DATETIME

SET @StartTime = GETDATE()

INSERT @Table
SELECT TABLE_NAME, TABLE_SCHEMA
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'

SELECT TOP 1 @CurrentId = Id, @TableName = '['+SchemaName+'].['+TableName+']', @Counter = Id FROM @Table

WHILE @CurrentId IS NOT NULL
    BEGIN
        SET @DynamicSql = 'UPDATE STATISTICS ' + @TableName -- This resets it to use the default sampling rate/amount
        EXEC(@DynamicSql)
        DELETE @Table WHERE Id = @CurrentId
        SET @CurrentId = NULL
        SELECT TOP 1 @CurrentId = Id, @TableName = '['+SchemaName+'].['+TableName+']', @Counter = Id FROM @Table
    END

SET @EndTime = GETDATE()

SET @DynamicSql = 'Finished running sp_autostats ''ON'' for ' + CAST(@Counter AS VARCHAR) + ' tables in '
    + CAST(DATEDIFF(second, @StartTime, @EndTime) AS VARCHAR) + ' seconds'
PRINT @DynamicSql