Drop all indexes in SQL Server
3 months ago
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.