Find and drop duplicate indexes

PROBLEM:
You want to find and drop duplicate indexes

SOLUTION:
Find duplicate indexes with same key columns in the same order, and the same included columns but in any order

WITH IX AS
  ( SELECT OBJECT_ID AS ID,
           INDEX_ID AS INDID,
           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 KEYNO,
               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 INC
   FROM SYS.INDEXES AS I)
SELECT OBJECT_SCHEMA_NAME(C1.ID) + '.' + OBJECT_NAME(C1.ID) AS TABLE_NAME,
       C1.NAME AS INDEX_NAME,
       C2.NAME AS DUPLICATE_INDEX,
       'DROP INDEX [' + C2.NAME + '] ON [' + OBJECT_SCHEMA_NAME(C1.ID) + '].[' + OBJECT_NAME(C1.ID) + ']' AS DROP_STATEMENT
FROM IX AS C1
JOIN IX AS C2 ON C1.ID = C2.ID
AND C1.INDID < C2.INDID
AND C1.COLS = C2.COLS
AND C1.INC = C2.INC;

If you find this solution useful, you are welcome to press one of the ads in this page.. Thanks!