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