Indexes on a table

Peter Schmitz

Staff member
Below is a small script that can be used to check whether or not a particular table in your database contains redundant indexes.

By redundant indexes I mean that if there is one index on columns A and B, and a second index on columns A, B, and C, generally speaking, it would be sufficient to drop the first index and remove the second one.

This script can help you identify such indexes, though I would suggest manually verifying the indexes anyway.

DECLARE @tableName sysname;
SET @tableName = 'myTable'; -- adjust for your own purposes

SELECT @tableName, * FROM
    SELECT AS indexname, AS columnname, ic.index_column_id FROM
        sys.indexes i
        JOIN sys.index_columns ic ON i.index_id = ic.index_id AND i.object_id = ic.object_id
        JOIN sys.columns c ON ic.column_id = c.column_id AND ic.object_id = c.object_id
        i.object_id = OBJECT_ID(@tableName)
) AS Source
    MIN(columnname) FOR 
    index_column_id IN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12], [13], [14], [15], [16])
) AS PivotTable
ORDER BY [1], [2], [3], [4], [5], indexname