Hi. I'm Stephen

This article was written in 2008. Some or all of the information and views may be out of date. Please keep this in mind while reading.

It has been a couple of weeks now since I last wrote anything here and so to my readers, I apologize. This morning I needed a solution to a problem and rather than search Google or ask a question on Stack Overflow, I decided to sit down for 5 minutes and see if I could find the solution myself.

The problem was that I needed an automated way to drop all the indexes from all the tables within the current database. I remembered I recently got some help for caching SQL Server Views and wondered if I could somehow adapt that existing code to delete all indexes.

First though, I needed to get a list of tables and indexes for the current database, this was done with the following Select statement.

Select
SysObjects.Name,
SysIndexes.Name
From
SysIndexes
Inner Join SysObjects On
        SysObjects.id = SysIndexes.id
Where
SysIndexes.Name Is Not Null
and SysObjects.Type = 'U'

According to the Transact-SQL Reference, you need both the table and index name in order to drop an index, which is why I have selected both of them in the above SQL Statement. Now all that needs to be done is to combine the code I used for caching SQL views with this one and we should be good to go.

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)

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

Drop Table #Indexes

I know the code is very young and perhaps the solution could be a bit more robust and elegant, this is why I have submitted it to RefactorMyCode. However, it does what I need and I just wanted to share it with you.

Please do not use this code unless you know exactly what you are doing.