SQL - Comparar Filas del resultado de una Consulta en SQL Server Enterprise 2017

SELECT SYS.TABLES.NAME AS NOMBRETABLA,SYS.INDEXES.NAME AS NOMBREINDICE,

	STUFF((SELECT ', ' + COLS.NAME + CASE WHEN NDX_COLS.IS_DESCENDING_KEY=1 THEN ' DESC'

																				ELSE ' ASC' END

			FROM SYS.TABLES TBLS

			INNER JOIN SYS.INDEX_COLUMNS NDX_COLS ON (NDX_COLS.OBJECT_ID = TBLS.OBJECT_ID)

			INNER JOIN SYS.COLUMNS COLS ON (COLS.OBJECT_ID = TBLS.OBJECT_ID) AND (NDX_COLS.COLUMN_ID= COLS.COLUMN_ID)

			WHERE TBLS.NAME = SYS.TABLES.NAME AND

			(SYS.INDEXES.OBJECT_ID = NDX_COLS.OBJECT_ID) AND (SYS.INDEXES.INDEX_ID = NDX_COLS.INDEX_ID)

			ORDER BY NDX_COLS.KEY_ORDINAL

			FOR XML PATH('')),1,1,'') COLUMN_INDX,

		LAG(STUFF((SELECT ', ' + COLS.NAME + CASE WHEN NDX_COLS.IS_DESCENDING_KEY=1 THEN ' DESC'

																				ELSE ' ASC' END

			FROM SYS.TABLES TBLS

			INNER JOIN SYS.INDEX_COLUMNS NDX_COLS ON (NDX_COLS.OBJECT_ID = TBLS.OBJECT_ID)

			INNER JOIN SYS.COLUMNS COLS ON (COLS.OBJECT_ID = TBLS.OBJECT_ID) AND (NDX_COLS.COLUMN_ID= COLS.COLUMN_ID)

			WHERE TBLS.NAME = SYS.TABLES.NAME AND

			(SYS.INDEXES.OBJECT_ID = NDX_COLS.OBJECT_ID) AND (SYS.INDEXES.INDEX_ID = NDX_COLS.INDEX_ID)

			ORDER BY NDX_COLS.KEY_ORDINAL

			FOR XML PATH('')),1,1,'')) OVER (ORDER BY SYS.INDEXES.NAME) COLUMNAS_INDX_COMP

FROM  SYS.TABLES

INNER JOIN SYS.INDEX_COLUMNS  ON (SYS.INDEX_COLUMNS.OBJECT_ID = SYS.TABLES.OBJECT_ID)

INNER JOIN SYS.INDEXES ON (SYS.INDEXES.OBJECT_ID = SYS.INDEX_COLUMNS.OBJECT_ID) AND (SYS.INDEXES.INDEX_ID = SYS.INDEX_COLUMNS.INDEX_ID)

INNER JOIN SYS.COLUMNS  ON (SYS.COLUMNS.OBJECT_ID = SYS.TABLES.OBJECT_ID) AND (SYS.INDEX_COLUMNS.COLUMN_ID=SYS.COLUMNS.COLUMN_ID)

WHERE SYS.INDEXES.IS_PRIMARY_KEY=0

GROUP BY SYS.TABLES.NAME, SYS.INDEXES.NAME, SYS.INDEXES.OBJECT_ID, SYS.INDEXES.INDEX_ID

ORDER BY SYS.INDEXES.NAME ASC;

EXECUTE master.sys.sp_MSforeachdb 'USE [?];

IF ''?'' NOT IN (''master'',''model'',''msdb'',''tempdb'')

BEGIN

;WITH IndexColumns AS(

SELECT DISTINCT schema_name (o.schema_id) AS ''SchemaName''

,object_name(o.object_id) AS TableName

,o.object_id

,i.Name AS IndexName

,i.index_id

,i.type,

(SELECT CASE key_ordinal WHEN 0 THEN NULL ELSE ''[''+col_name(k.object_id,column_id) +'']'' END AS [data()]

FROM sys.index_columns as k (NOLOCK)

WHERE k.object_id = i.object_id

AND k.index_id = i.index_id

ORDER BY key_ordinal, column_id

FOR XML PATH('''')) AS cols

FROM sys.indexes (NOLOCK) AS i

INNER JOIN sys.objects o (NOLOCK) ON i.object_id =o.object_id

INNER JOIN sys.index_columns ic (NOLOCK) ON ic.object_id =i.object_id and ic.index_id =i.index_id

INNER JOIN sys.columns c (NOLOCK) ON c.object_id = ic.object_id and c.column_id = ic.column_id

WHERE i.object_id in (SELECT object_id from sys.objects (NOLOCK) WHERE type =''U'') AND i.index_id <>0 AND i.type <>3 AND i.type <>6

GROUP BY o.schema_id,o.object_id,i.object_id,i.Name,i.index_id,i.type

)

SELECT db_name(),ic1.SchemaName,ic1.TableName,ic1.IndexName,ic2.IndexName as DuplicateIndexName, ic1.cols as IndexCols

FROM IndexColumns ic1 JOIN IndexColumns ic2 ON ic1.object_id = ic2.object_id AND ic1.index_id < ic2.index_id AND ic1.cols = ic2.cols

ORDER BY 1,2,3

END'

EXECUTE master.sys.sp_MSforeachdb 'USE [?];

IF ''?'' NOT IN (''master'',''model'',''msdb'',''tempdb'')

BEGIN

;WITH IndexColumns as(

SELECT DISTINCT

schema_name (o.schema_id) AS ''SchemaName''

,object_name(o.object_id) AS TableName

,i.Name AS IndexName

,o.object_id

,i.index_id

,i.type

,(SELECT CASE key_ordinal WHEN 0 THEN NULL ELSE ''[''+col_name(k.object_id,column_id) +'']'' END AS [data()]

FROM sys.index_columns AS k WHERE k.object_id = i.object_id AND k.index_id = i.index_id

ORDER BY key_ordinal, column_id FOR XML PATH('''')) AS cols

FROM sys.indexes AS i INNER JOIN sys.objects o ON i.object_id =o.object_id

INNER JOIN sys.index_columns ic ON ic.object_id =i.object_id AND ic.index_id =i.index_id

INNER JOIN sys.columns c ON c.object_id = ic.object_id AND c.column_id = ic.column_id

WHERE i.object_id in (SELECT object_id FROM sys.objects WHERE type =''U'') AND i.index_id <>0 AND i.type <>3 AND i.type <>6

GROUP BY o.schema_id,o.object_id,i.object_id,i.Name,i.index_id,i.type

)

SELECT db_name(),

ic1.SchemaName,ic1.TableName,ic1.IndexName,ic1.cols AS IndexCols,ic2.IndexName AS RedundantIndexName, ic2.cols AS RedundantIndexCols

FROM IndexColumns ic1

JOIN IndexColumns ic2 ON ic1.object_id = ic2.object_id

AND ic1.index_id <> ic2.index_id

AND ic1.cols <> ic2.cols

AND ic2.cols LIKE REPLACE(ic1.cols,''['',''[[]'') + '' %''

ORDER BY 1,2,3,5

END'