/*
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