|
Execute the following
Microsoft SQL Server T-SQL script in SSMS Query Editor to update statistics with full scan on
all tables:
-- Update all statistics for the database
EXEC sp_updatestats
------------
-- Update statistics for a group of tables with FULL SCAN
USE AdventureWorks2008;
GO
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
/*
UPDATING STATISTICS FOR TABLE: Production.ProductInventory
UPDATING STATISTICS FOR TABLE: Sales.SpecialOffer
UPDATING STATISTICS FOR TABLE: Person.Address
.... */
Related article:
UPDATE STATISTICS (Transact-SQL)
|