SQLUSA

Microsoft SQL Server 2005 Best Practices

How to drop all stats on a table?

 

Execute the following script in Query Editor to delete all stats on the SalesOrderDetail table:

USE AdventureWorks
GO

DECLARE AllStats CURSOR FOR
SELECT Name FROM sys.stats
WHERE object_id = object_id('Sales.SalesOrderDetail') AND auto_created <> 0

DECLARE @StatName NVARCHAR(512)

OPEN AllStats

FETCH next FROM AllStats INTO @StatName

WHILE @@FETCH_STATUS = 0
BEGIN
EXEC ('drop statistics Sales.SalesOrderDetail.' + @StatName)
FETCH NEXT FROM AllStats INTO @StatName
END

CLOSE AllStats
DEALLOCATE AllStats
GO



 

The World Leader in SQL Server Training
 
SQLUSA.com Home Page