Drop all indexes from a SQL table - Tiho's blog - Site Home

After spending some time to find out that there is no easy way to drop all indexes from a SQL table I came up with this script.

DECLARE @indexName NVARCHAR(128)
DECLARE @dropIndexSql NVARCHAR(4000)
DECLARE tableIndexes CURSOR FOR
SELECT name FROM sysindexes
WHERE id = OBJECT_ID(N'tableName') AND 
  indid > 0 AND indid < 255 AND
  INDEXPROPERTY(id, name, 'IsStatistics') = 0
ORDER BY indid DESC
OPEN tableIndexes
FETCH NEXT FROM tableIndexes INTO @indexName
WHILE @@fetch_status = 0
BEGIN
  SET @dropIndexSql = N'DROP INDEX tableName.' + @indexName
  EXEC sp_executesql @dropIndexSql
  FETCH NEXT FROM tableIndexes INTO @indexName
END
CLOSE tableIndexes
DEALLOCATE tableIndexes

Use it at your own discretion. It should work fine on both SQL Server 2000 and SQL Server 2005.

Update on September 1st, 2006:

Thanks to Anthony Jones who pointed out that the script should drop the indexes in decscending order of indid. If the table has a clustered index it will be indid 1 which if dropped first will cause all other indexes to be rebuilt only to be subsequently dropped. I updated the script. Enjoy!