The purpose of this query is to find identical duplicate non-clustered indexes i.e. indexes on same table with same columns in same order. If you have a need to find indexes having same columns but in any order, set value for the variable @disregard_column_order = 1 in the code.
The query excludes identical indexes where one is clustered and the other one is non-clustered index. But you can again toggle that by setting value for variable @include_clustered_indexes = 1.
/*
whether to include identical indexes where one is clustered and
the other one is non-clustered index
*/
DECLARE @include_clustered_indexes bit = 0
/*
whether to find duplicate indexes where although all columns are same, they may not be in same order
*/
DECLARE @disregard_column_order bit = 0
index_columns=COALESCE((STUFF((SELECTCAST(','+COL_NAME(object_id, column_id)ASvarchar(max))
WHERE (object_id= i.object_idAND index_id = i.index_id)
ORDERBYobject_id, index_id,
CASEWHEN @disregard_column_order = 1 then column_id else key_ordinal end
FORxmlPATH ('')), 1, 1,'')),''),
INNERJOINsys.objects o ON o.object_id= i.object_id
WHEREOBJECTPROPERTY(o.object_id,'ismsshipped')= 0 AND index_id != 0
AND i.index_id >CASEWHEN @include_clustered_indexes = 1 THEN 0 ELSE 1 END
-- AND i.index_id != 1 -- comment this line if you want find indexes identical to clustered indexes as well
SCHEMA_NAME(i1.schema_id)schema_name,
OBJECT_NAME(i1.object_id)object_name,
INNERJOIN(SELECTschema_id,type_desc,object_id,index_columns
GROUPBYschema_id,type_desc,object_id,index_columns
ON i1.schema_id= i2.schema_id
AND i1.type_desc= i2.type_desc
AND i1.object_id= i2.object_id
AND i1.index_columns= i2.index_columns
ORDERBYschema_name, i1.type_desc,object_name, i1.index_name