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
Refactorings
No refactoring yet !
Rik Hemsley
October 7, 2008, October 07, 2008 11:17, permalink
Just wondering: Why are you dropping all indexes?
GateKiller
October 7, 2008, October 07, 2008 11:55, permalink
Rik,
I knew sooner or later someone would ask that question :)
I'm working on producing some complex reports using a 3rd party database. Using the databases current indexes, which I cannot change, the SQL statement takes about 20hours to complete. If copy the database and replace the indexes with my own, it takes about 2 minutes. So I was looking for a quick way to remove the original index quickly so I could insert my own.
rikkus
October 7, 2008, October 07, 2008 12:13, permalink
I had to ask because often people want to recreate the indexes, in which case DBCC REINDEX is a much better idea.
Here's my version, which is pretty much the same as yours, except uses a cursor as it's a little neater.
You may want to add a check that you're not operating on sysdiagrams, as this logic will drops its indexes, too.
DECLARE @indexName VARCHAR(128) DECLARE @tableName VARCHAR(128) DECLARE [indexes] CURSOR FOR SELECT [sysindexes].[name] AS [Index], [sysobjects].[name] AS [Table] FROM [sysindexes] INNER JOIN [sysobjects] ON [sysindexes].[id] = [sysobjects].[id] WHERE [sysindexes].[name] IS NOT NULL AND [sysobjects].[type] = 'U' OPEN [indexes] FETCH NEXT FROM [indexes] INTO @indexName, @tableName WHILE @@FETCH_STATUS = 0 BEGIN PRINT 'DROP INDEX [' + @indexName + '] ON [' + @tableName + ']' FETCH NEXT FROM [indexes] INTO @indexName, @tableName END CLOSE [indexes] DEALLOCATE [indexes]
Moonshield
November 11, 2008, November 11, 2008 23:42, permalink
You can also reuse the example I gave you days ago
http://refactormycode.com/codes/497-backup-all-ms-sql-server-databases
The below is some code I recently wrote that attempts to delete all indexes in the current database. It's my first attempt and it's not a very elegant solution so I'm open to suggestions on how I can improve the code.
Thanks