Wednesday, July 28, 2010

UPDATE STATISTICS for all database tables

/***** BEGIN - UPDATE STATISTICS *****/
DECLARE @t TABLE(RowID INT IDENTITY(1,1), SchemaName VARCHAR(128), TableName VARCHAR(128))

INSERT INTO @t(SchemaName, TableName)
SELECT t.TABLE_SCHEMA, t.TABLE_NAME FROM INFORMATION_SCHEMA.TABLES t
WHERE t.table_type = 'BASE TABLE'

DECLARE @Rows INT, @i INT;

SELECT @Rows = (SELECT COUNT(RowID) FROM @t), @i = 1;

WHILE(@i <= @Rows)
BEGIN
 DECLARE @sqlCommand NVARCHAR(512);

 SELECT @sqlCommand =
     'UPDATE STATISTICS ' +
     t.SchemaName + '.' +
     t.TableName +
     ' WITH FULLSCAN'
 FROM @t t
 WHERE t.RowID = @i;

 PRINT @sqlCommand
 EXEC sp_executesql @sqlCommand;

 SET @i = @i + 1;
END
GO
/***** END - UPDATE STATISTICS *****/

No comments: