SQL Server - Delete all statistics from a table

17 October,2007 by Jack Vamvas

Use this script to delete all statistics from a given table in SQL 2005.

/******************************************************************
File Name    : delete-statistics-from-table.sql
-- Author       : Jack Vamvas
-- Description  :Delete statistics from table
-- Last Modified:17/10/2007

******************************************************************/
DECLARE @db_Table VARCHAR(200)
SET @db_Table =  'MyTable'

DECLARE TableStats CURSOR FOR
SELECT Name FROM sys.stats
WHERE object_id = object_id(@db_Table) AND auto_created <> 0 
DECLARE @StatName NVARCHAR(512)

OPEN TableStats

FETCH next FROM TableStats INTO @StatName

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

CLOSE TableStats
DEALLOCATE TableStats
GO

See Also

SQL Server sp_updatestats and UPDATE STATISTICS


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on SQL Server - Delete all statistics from a table


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer