TSQL - Partial Duplicate Indexes

Paul Nielsen has another T-SQL code which helps to find partial duplicate indexes that share leading key columns, e.g. Ix1(col1, col2, col3) and Ix2(col1, col2) which would be considered duplicate indexes. This query only examines key columns and does not consider included columns.

WITH indexcols 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 FROM sys.indexes AS i ) SELECT object_schema_name(c1.id) + '.' + object_name(c1.id) AS 'Table Name' , c1.name AS 'Index' , c2.name AS 'Partial Duplicate' FROM indexcols AS c1 JOIN indexcols AS c2 ON c1.id = c2.id AND c1.indid < c2.indid AND c1.cols <> c2.cols -- remove exact duplicates AND ((c1.cols like c2.cols + '%' AND SUBSTRING(c1.cols,LEN(c2.cols)+1,1) = ' ') OR (c2.cols like c1.cols + '%' AND SUBSTRING(c2.cols,LEN(c1.cols)+1,1) = ' ')) ORDER BY [Table Name]

TSQL - Partial Duplicate Indexes