Database

How To Drop All Indexes From A Database

If you're new here, you may want to subscribe to my RSS feed or become a fan of this blog on Facebook. Thanks for visiting!

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]

Hope this was helpful!

Hatim