Drop All Indexes and Stats in one Script

I am not sure why someone would want to do this, but it was asked on the forums, and I figured I would post the code I created to perform such a nightmarish operation.  As with any post that I make providing code that could be potentially damaging and dangerous, if you use it, you do so at your own risk.  Don't send me emails complaining that you got fired for deleting all the indexes with the scripts on this post.  I am not going to be able to help you fix it, and my recommendation is going to be restore a backup, and start scripting them all off if you still have a job.

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


The above script will work for SQL 2005 and 2008 also, but only because compatibility views have been carried forward in code by Microsoft. The correct code for doing this in SQL 2005 and 2008 is as follows:

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


Hope it helps someone out.