I realized yesterday I posted this quickly after testing only against SQL 2016 version. So I decided to also test against SQL 2014 and 2012 versions to make sure its compatible. I made one tweak (removed the compression_delay field from the output) to make it possible.
So please give it a try and let me know your results, perspectives and feedback!
As I had mentioned in my original post, I was looking to find identical duplicate non-clustered indexes i.e. indexes on same table with same columns in same order. The query below uses two variables to make it little bit more flexible.
If you have a need or just curious 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
whether to find duplicate indexes where although all columns are same, they may not be in same order
COALESCE((STUFF((SELECT CAST(',' +
COL_NAME(object_id, column_id) AS varchar(max))
object_id = i.object_id AND
index_id = i.index_id
else key_ordinal end
Now among other things, the query does not take into account the ASC or DESC clause of the index. So you may have two identical indexes but one is sorted ASC and the other one is DESC and you may have a very good reason for that. Nor does this query consider if one or both indexes are filtered indexes. I would like to hear your feedbacks before putting more efforts to cover every other possible options.