How To Drop All Indexes From A Database

I was doing some performance tweaking of a batch job that was hanging and i was suspecting the indexes in the database to have something to do with it shocking up so I used this script to clear all indexes.

Ironically it was a missing index that caused the bottleneck. But here it is for anyone that might need it.

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]

Read more: Hatim’s Development Blog

Posted via email from jasper22's posterous