Transact-SQL
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31
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.
Transact-SQL
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30
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