Query to Identify Duplicate Indexes in SQL Server

-- =============================================

-- Author:                   MalleswaraReddy

-- Create date: 26-09-2014

-- Description:            The below script is used to identify the exact duplicate index on a table.

-- =============================================

;WITH CTE AS

(

SELECT so.name as TableName,si.name AS IndexName,sc.name AS Name,ic.key_ordinal as KeyOrder ,

CASE si.is_unique   WHEN 1 THEN 'UNIQUE ' ELSE 'NON-UNIQUE ' END + si.type_desc AS type_desc

from sys.indexes si

join sys.tables so on si.object_id = so.object_id

join sys.index_columns ic on si.index_id = ic.index_id and ic.object_id = si.object_id

join sys.columns sc on  sc.column_id = ic.column_id and ic.object_id = sc.object_id

),

 CT AS

(

select  Row_number () Over( order by TableName,IndexName) AS Row,

                         T2.TableName ,

                         T2.IndexName,

                         T2.type_desc AS IndexType,

                                                                  STUFF((SELECT ','+T.name

                                                                                    FROm CTE T

                                                                                    WHERE (T.TableName = T2.TableName AND T.IndexName = T2.IndexName)

                                                                                    ORDER BY T.TableName,T.IndexName,T.KeyOrder

                                                                                    FOR XML PATH('')),1,1,'') AS IndexColumns

                                                                                   

FROM CTE T2

GROUP BY TableName,IndexName,type_desc

)

Select Row,TableName,IndexName,IndexColumns,IndexType FROM CT

WHERE Row IN

(

  SELECT max(Row) FROM CT

  GROUP BY TableName,IndexColumns

  Having Count(*) > 1

)

UNION

Select Row,TableName,IndexName,IndexColumns,IndexType FROM CT

WHERE Row IN

(

  SELECT MIN(Row) FROM CT

  GROUP BY TableName,IndexColumns

  Having Count(*) > 1

)

ORDER BY TableName,IndexColumns