How to a remove all database indexes from a SQL server database

Question

I am about to write a script to add new indexes to a database, I wish that script to work regardless of the current indexes the database has.

Therefore the first step in the script should be to remove all the current indexes. (I could just as easily run .net code, but I think TSQL will be less painful for this type of thing)

(This is for use in development for small databases, so I don’t mind if it is not the quickest way of managing indexes, this needs to work for SqlServer 2005 and SqlServer 2008)

Solution 1

I found this at http://refactormycode.com/codes/522-drop-all-indexes-in-sql-server. It appears to drop all indexes in the current database:

Declare @Index varchar(128)
Declare @Table varchar(128)

Select
SysIndexes.Name As 'Index',
SysObjects.Name As 'Table'
Into
#Indexes
From
SysIndexes
Inner Join SysObjects On
    SysObjects.id = SysIndexes.id
Where
SysIndexes.Name Is Not Null
and SysObjects.XType = 'U'
Order By
SysIndexes.Name,
SysObjects.Name

While (Select Count(*) From #Indexes) > 0
Begin
    Set @Index = (Select Top 1 [Index] From #Indexes)
    Set @Table = (Select Top 1 [Table] From #Indexes)

    --Print 'Drop Index [' + @Index + '] On [' + @Table + ']' + Char(13)
    Exec ('Drop Index [' + @Index + '] On [' + @Table + ']')
    Delete From #Indexes Where [Index] = @Index and [Table] = @Table
End

Drop Table #Indexes

Solution 2

Another working script:

http://blogs.msdn.com/b/tihot/archive/2006/01/16/513548.aspx


    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