Indexes on a table

Peter Schmitz

Administrator
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.

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

SELECT @tableName, * FROM
(
    SELECT i.name AS indexname, c.name 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
    WHERE 
        i.object_id = OBJECT_ID(@tableName)
) AS Source
PIVOT
(
    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
;
 
Top