Here is a script I wrote to generate a script to drop all indexes that are not primary keys. This works for Sql Server 2005. The main thing it uses is sp_MSForEachTable, an undocumented stored procedure. Run the generated script, and you will be good to go.

This is especially useful before a bulk load or bulk insert, where you load a lot of data into Sql Server. It is best and faster to drop all those indexes, load the data, and then recreate them.

I am assuming that you have saved the index creation scripts in a separate file, so they can be easily recreated. If you don’t want to bother, uncomment the commented line, and all indexes will be dropped, except primary keys.

Enjoy!

declare @RETURN_VALUE int
declare @command1 nvarchar(2000)
set @command1 = 'DECLARE @indexName NVARCHAR(128)'
set @command1 = @command1 + ' DECLARE @dropIndexSql NVARCHAR(4000)'
set @command1 = @command1 + ' DECLARE tableIndexes CURSOR FAST_FORWARD FOR'
set @command1 = @command1 + ' SELECT name FROM sys.indexes'
set @command1 = @command1 + ' WHERE object_id = OBJECT_ID(''?'') AND index_id > 0 AND index_id < 255 AND is_primary_key = 0'
set @command1 = @command1 + ' ORDER BY index_id DESC'
set @command1 = @command1 + ' OPEN tableIndexes FETCH NEXT FROM tableIndexes INTO @indexName'
set @command1 = @command1 + ' WHILE @@fetch_status = 0'
set @command1 = @command1 + ' BEGIN'
set @command1 = @command1 + ' SET @dropIndexSql = N''DROP INDEX ?.['' + @indexName + '']'''
--set @command1 = @command1 + ' EXEC sp_executesql @dropIndexSql'
set @command1 = @command1 + ' print @dropIndexSql'
set @command1 = @command1 + ' FETCH NEXT FROM tableIndexes INTO @indexName'
set @command1 = @command1 + ' END'
set @command1 = @command1 + ' CLOSE tableIndexes'
set @command1 = @command1 + ' DEALLOCATE tableIndexes'
Print '-----------------------------------------'
exec @RETURN_VALUE = sp_MSforeachtable @command1=@command1

And here is a script to confirm the name and number of indexes that will be dropped by the above script:

SELECT name, object_name(object_id) TableName FROM sys.indexes
WHERE index_id > 0 AND index_id < 255 AND is_primary_key = 0 
and object_name(object_id) not like 'sys%' and object_name(object_id) not like 'queue%'