Search

Saturday, January 14, 2012

Drop All Indexes and Stats in one Script


SQL Server 2005/2008
DECLARE @ownername SYSNAME
DECLARE @tablename SYSNAME
DECLARE @indexname SYSNAME
DECLARE @sql NVARCHAR(4000) DECLARE dropindexes CURSOR FOR

SELECT indexes.nameobjects.nameschemas.name FROM sys.indexes JOIN sys.objects ON indexes.OBJECT_ID = objects.OBJECT_ID JOIN sys.schemas ON objects.schema_id schemas.schema_id WHERE indexes.index_id 0
  
AND indexes.index_id 255
  
AND objects.is_ms_shipped 0
  
AND NOT EXISTS (SELECT FROM sys.objects WHERE objects.name indexes.name) ORDER BY objects.OBJECT_ID, indexes.index_id DESC


SELECT * FROM sys.stats OPEN dropindexes FETCH NEXT FROM dropindexes INTO @indexname, @tablename, @ownername WHILE @@fetch_status = BEGIN
  SET @sql = N'DROP INDEX '+QUOTENAME(@ownername)+'.'+QUOTENAME(@tablename)+'.'+QUOTENAME(@indexname)
  PRINT @sql
  EXEC sp_executesql @sql 
  FETCH NEXT FROM dropindexes INTO @indexname, @tablename, @ownername END
CLOSE dropindexes DEALLOCATE dropindexes

GO 
DECLARE @ownername SYSNAME
DECLARE @tablename SYSNAME
DECLARE @statsname SYSNAME
DECLARE @sql NVARCHAR(4000) DECLARE dropstats CURSOR FOR

SELECT stats.nameobjects.nameschemas.name FROM sys.stats JOIN sys.objects ON stats.OBJECT_ID = objects.OBJECT_ID JOIN sys.schemas ON objects.schema_id schemas.schema_id WHERE stats.stats_id 0
  
AND stats.stats_id 255
  
AND objects.is_ms_shipped ORDER BY objects.OBJECT_ID, stats.stats_id DESC

OPEN dropstats FETCH NEXT FROM dropstats INTO @statsname, @tablename, @ownername WHILE @@fetch_status = BEGIN
  SET @sql = N'DROP STATISTICS '+QUOTENAME(@ownername)+'.'+QUOTENAME(@tablename)+'.'+QUOTENAME(@statsname)
  EXEC sp_executesql @sql 
  --PRINT @sql
  FETCH NEXT FROM dropstats INTO @statsname, @tablename, @ownername END
CLOSE dropstats DEALLOCATE dropstats 
SQL Server 2000 Code

DECLARE @ownername SYSNAME
DECLARE @tablename SYSNAME
DECLARE @indexname SYSNAME
DECLARE @sql NVARCHAR(4000) DECLARE dropindexes CURSOR FOR


SELECT sysindexes.name, sysobjects.name, sysusers.name FROM sysindexes JOIN sysobjects ON sysindexes.id = sysobjects.id JOIN sysusers ON sysobjects.uid = sysusers.uid WHERE indid > 0
  AND indid < 255
  AND INDEXPROPERTY(sysobjects.id, sysindexes.name, 'IsStatistics') = 0
  AND sysobjects.TYPE = N'U'
  AND NOT EXISTS (SELECT 1 FROM sysobjects WHERE sysobjects.name = sysindexes.name) ORDER BY sysindexes.id, indid DESC


OPEN dropindexes FETCH NEXT FROM dropindexes INTO @indexname, @tablename, @ownername WHILE @@fetch_status = 0 BEGIN
  SET @sql = N'DROP INDEX '+QUOTENAME(@ownername)+'.'+QUOTENAME(@tablename)+'.'+QUOTENAME(@indexname)
  PRINT @sql
  EXEC sp_executesql @sql  
  FETCH NEXT FROM dropindexes INTO @indexname, @tablename, @ownername END
CLOSE dropindexes DEALLOCATE dropindexes


GO DECLARE @ownername SYSNAME
DECLARE @tablename SYSNAME
DECLARE @statsname SYSNAME
DECLARE @sql NVARCHAR(4000) DECLARE dropstats CURSOR FOR


SELECT sysindexes.name, sysobjects.name, sysusers.name FROM sysindexes JOIN sysobjects ON sysindexes.id = sysobjects.id JOIN sysusers ON sysobjects.uid = sysusers.uid WHERE indid > 0
  AND indid < 255
  AND INDEXPROPERTY(sysobjects.id, sysindexes.name, 'IsStatistics') = 1
  AND sysobjects.TYPE = N'U';
OPEN dropstats FETCH NEXT FROM dropstats INTO @statsname, @tablename, @ownername WHILE @@fetch_status = 0 BEGIN
  SET @sql = N'DROP STATISTICS '+QUOTENAME(@ownername)+'.'+QUOTENAME(@tablename)+'.'+QUOTENAME(@statsname)
  EXEC sp_executesql @sql  
  --PRINT @sql
  FETCH NEXT FROM dropstats INTO @statsname, @tablename, @ownername END
CLOSE dropstats 
DEALLOCATE dropstats 

No comments:

Post a Comment