dinsdag, april 03, 2007

Drop all statistics - 2005

DECLARE @ObjectName sysname
DECLARE @StatsName sysname
DECLARE StatsCursor CURSOR FAST_FORWARD
FOR
SELECT OBJECT_NAME(object_id) as 'ObjectName', [name] as 'StatsName' FROM sys.stats
WHERE (INDEXPROPERTY(object_id, [name], 'IsAutoStatistics') = 1 OR INDEXPROPERTY(object_id, [name], 'IsStatistics') = 1)
AND OBJECTPROPERTY(object_id, 'IsMSShipped') = 0
OPEN StatsCursor
FETCH NEXT FROM StatsCursor
INTO @ObjectName, @StatsName
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC ('DROP STATISTICS ' + @ObjectName + '.' + @StatsName)
FETCH NEXT FROM StatsCursor
INTO @ObjectName, @StatsName
END
CLOSE StatsCursor
DEALLOCATE StatsCursor

posted by WesleyB @ 11:40 a.m.   1 comments

1 Comments:

At 8/04/2008 9:01 a.m., Blogger Unknown said...

HI, There is little modification in the script is that adding [] with object Names. Because if someone has table names as system key words then it will through error. That's why there was need to introduce of []. Modified script is as below:

DECLARE @ObjectName sysname
DECLARE @StatsName sysname
DECLARE StatsCursor CURSOR FAST_FORWARD
FOR
SELECT
'['+OBJECT_NAME(object_id)+']' as 'ObjectName',
'['+[name]+']' as 'StatsName'
FROM sys.stats
WHERE
(INDEXPROPERTY(object_id, [name], 'IsAutoStatistics') = 1
OR INDEXPROPERTY(object_id, [name], 'IsStatistics') = 1)
AND OBJECTPROPERTY(object_id, 'IsMSShipped') = 0
OPEN StatsCursor
FETCH NEXT FROM StatsCursor
INTO @ObjectName, @StatsName
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC ('DROP STATISTICS ' + @ObjectName + '.' + @StatsName)
FETCH NEXT FROM StatsCursor
INTO @ObjectName, @StatsName
END
CLOSE StatsCursor
DEALLOCATE StatsCursor


Thanks & With Best Regards
Bhudev

 

Een reactie posten

<< Home