Finding duplicate indexes

It is always a good idea to check your databases for duplicate indexes. Duplicate indexes don’t do any good. The first question you ask yourself is why is it there in the first place? It might be the case it is not there but this is something that I have encountered in various occasions and it doesn’t harm to check if there are any duplicate indexes in your database. The next step is how to check; here I have tried to do this. The following script will help you find duplicate indexes on up to 5 columns. If you have got indexes on more that five columns, you can extend it to as many column as you want.

SELECT l1.tablename,
l1.indexname,
l2.indexname AS duplicateIndex
FROM
(SELECT tbl.[name] AS TableName,
idx.[name] AS IndexName,
INDEX_COL( tbl.[name], idx.indid, 1 ) AS col1,
INDEX_COL( tbl.[name], idx.indid, 2 ) AS col2,
INDEX_COL( tbl.[name], idx.indid, 3 ) AS col3,
INDEX_COL( tbl.[name], idx.indid, 4 ) AS col4,
INDEX_COL( tbl.[name], idx.indid, 5 ) AS col5
FROM SYSINDEXES idx
INNER JOIN SYSOBJECTS tbl ON idx.[id] = tbl.[id]
WHERE indid > 0
AND INDEXPROPERTY( tbl.[id], idx.[name], 'IsStatistics') = 0 ) l1
JOIN
(SELECT tbl.[name] AS TableName,
idx.[name] AS IndexName,
INDEX_COL( tbl.[name], idx.indid, 1 ) AS col1,
INDEX_COL( tbl.[name], idx.indid, 2 ) AS col2,
INDEX_COL( tbl.[name], idx.indid, 3 ) AS col3,
INDEX_COL( tbl.[name], idx.indid, 4 ) AS col4,
INDEX_COL( tbl.[name], idx.indid, 5 ) AS col5
FROM SYSINDEXES idx
INNER JOIN SYSOBJECTS tbl ON idx.[id] = tbl.[id]
WHERE indid > 0
AND INDEXPROPERTY( tbl.[id], idx.[name], 'IsStatistics') = 0) l2
ON l1.tablename = l2.tablename
AND l1.indexname <> l2.indexname
AND l1.col1 = l2.col1
AND COALESCE(l1.col2,'') = COALESCE(l2.col2,'')
AND COALESCE(l1.col3,'') = COALESCE(l2.col3,'')
AND COALESCE(l1.col4,'') = COALESCE(l2.col4,'')
AND COALESCE(l1.col5,'') = COALESCE(l2.col5,'')


Once you find your duplicate indexes, remove non-clustered indexes if one of the indexes is clustered.