gistfile1.txt

with indexcols as (
    select
        object_id as table_id,
        index_id,
        name as index_name,
        (
            select case keyno when 0 then null else colid end as [data()]
            from sys.sysindexkeys as k
            where k.id = i.object_id
            and k.indid = i.index_id
            order by colid
            for xml path('')
        ) as cols,
        (
            select case keyno when 0 then colid else null end as [data()]
            from sys.sysindexkeys as k
            where k.id = i.object_id
            and k.indid = i.index_id
            order by colid
            for xml path('')
        ) as included_columns
    from sys.indexes as i
    where is_unique <> 1
)
select
    db_name(),
    object_schema_name(c1.table_id) + '.' + object_name(c1.table_id),
    c1.index_name, c1.cols, c1.included_columns,
    c2.index_name, c2.cols, c2.included_columns,
    'drop index [' + c2.index_name + '] on [' + object_schema_name(c2.table_id) + '].[' + object_name(c2.table_id) + ']'
from indexcols as c1
inner join indexcols as c2
    on c1.table_id = c2.table_id
    and c1.cols = c2.cols
    and c1.index_id <> c2.index_id
where 1=1
and len(c1.included_columns) >= len(c2.included_columns)
order by len(c1.included_columns) + len(c2.included_columns)