Полные дубликаты

WITH IndexSchema AS (
SELECT i.object_id
,i.index_id
,i.name
,ISNULL(i.filter_definition,'') AS filter_definition
,i.is_unique
,(SELECT CASE key_ordinal WHEN 0 THEN NULL ELSE QUOTENAME(CAST(column_id AS VARCHAR)
+ CASE WHEN ic.is_descending_key = 1 THEN '-' ELSE '+' END,'(') END
FROM sys.index_columns ic
WHERE ic.object_id = i.object_id
AND ic.index_id = i.index_id
ORDER BY key_ordinal, column_id
FOR XML PATH('')) AS index_columns_keys_ids
,(SELECT CASE key_ordinal WHEN 0 THEN QUOTENAME(column_id,'(') ELSE NULL END
FROM sys.index_columns ic
WHERE ic.object_id = i.object_id
AND ic.index_id = i.index_id
ORDER BY column_id
FOR XML PATH('')) AS included_columns_ids
FROM sys.tables t
INNER JOIN sys.indexes i ON t.object_id = i.object_id
WHERE i.type_desc IN ('NONCLUSTERED'))
SELECT QUOTENAME(DB_NAME()) AS database_name
,QUOTENAME(OBJECT_SCHEMA_NAME(is1.object_id)) + '.'
+ QUOTENAME(OBJECT_NAME(is1.object_id)) AS object_name
,is1.name as index_name
,is2.name as duplicate_index_name
FROM IndexSchema is1
INNER JOIN IndexSchema is2 ON is1.object_id = is2.object_id
AND is1.index_id <> is2.index_id
AND is1.index_columns_keys_ids = is2.index_columns_keys_ids
AND is1.included_columns_ids = is2.included_columns_ids
AND is1.filter_definition = is2.filter_definition
AND is1.is_unique = is2.is_unique