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.name, objects.name, schemas.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 1 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 = 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 stats.name, objects.name, schemas.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 = 0 ORDER BY objects.OBJECT_ID, stats.stats_id DESC

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 

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