LearnItFirst User Forum

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

    Description: This script turns on auto stats for every table and it's indexes.
   
    Versions: SQL Server 2005, 2000, 7.0 but really for 2000 and 7.0
   
    Creation Date: Sept 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 = 'EXEC sp_autostats ''' + @TableName + ''', ''ON'''
        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