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

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 th开发者_开发知识库e 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)

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

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