1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97
| WITH
TK AS
(
-- clés
SELECT o.object_id, i.index_id, i.type_desc, i.filter_definition,
STUFF((SELECT ', ' + c.name + CASE WHEN ic.is_descending_key = 1 THEN ' DESC' ELSE '' END
FROM sys.index_columns AS ic
INNER JOIN sys.columns AS c
ON ic.object_id = c.object_id
AND ic.column_id = c.column_id
WHERE ic.object_id = i.object_id AND ic.index_id = i.index_id
AND ic.key_ordinal > 0
AND ic.is_included_column = 0
ORDER BY ic.key_ordinal
FOR XML PATH('')), 1, 2, '') AS KEY_LIST,
STUFF((SELECT ', ~' + CAST(ic.column_id AS VARCHAR(32)) + CASE WHEN ic.is_descending_key = 1 THEN ' DESC' ELSE '' END + '~'
FROM sys.index_columns AS ic
WHERE ic.object_id = i.object_id AND ic.index_id = i.index_id
AND ic.key_ordinal > 0
AND ic.is_included_column = 0
ORDER BY ic.key_ordinal
FOR XML PATH('')), 1, 2, '') AS KEY_ID_LIST
FROM sys.indexes AS i
JOIN sys.objects AS o ON i.object_id = o.object_id
WHERE o.is_ms_shipped = 0 AND index_id > 0
),
TI AS
(
-- colonnes incluses
SELECT o.object_id, i.index_id,
STUFF((SELECT ', ' + c.name
FROM sys.index_columns AS ic
INNER JOIN sys.columns AS c
ON ic.object_id = c.object_id
AND ic.column_id = c.column_id
WHERE ic.object_id = i.object_id AND ic.index_id = i.index_id
AND ic.key_ordinal = 0
AND ic.is_included_column = 1
ORDER BY c.name
FOR XML PATH('')), 1, 2, '') AS INC_LIST
FROM sys.indexes AS i
JOIN sys.objects AS o ON i.object_id = o.object_id
WHERE o.is_ms_shipped = 0 AND index_id > 0
AND EXISTS(SELECT *
FROM sys.index_columns AS ic
WHERE ic.object_id = i.object_id AND ic.index_id = i.index_id
AND ic.is_included_column = 1)
),
TS AS
(
-- synthèses
SELECT TK.object_id, TK.index_id, TK.type_desc, TK.filter_definition, TK.KEY_LIST, TK.KEY_ID_LIST, TI.INC_LIST,
1 + LEN(KEY_ID_LIST) - LEN(REPLACE(KEY_ID_LIST, ',', '')) AS COLS
FROM TK
LEFT OUTER JOIN TI ON TK.object_id = TI.object_id AND TI.index_id = TK.index_id
),
TX AS
(
-- comparaisons
SELECT T2.object_id, T2.index_id,
T3.index_id AS index_id_anomalie,
T2.KEY_LIST AS CLEF_INDEX,
T3.KEY_LIST AS CLEF_INDEX_ANORMAL,
T2.INC_LIST, T3.INC_LIST AS COLONNES_INCLUSES_ANORMAL,
CASE
WHEN T2.KEY_ID_LIST = T3.KEY_ID_LIST
THEN 'DOUBLONS'
WHEN T2.KEY_ID_LIST LIKE T3.KEY_ID_LIST +'%'
THEN 'INCLUS'
END AS ANOMALIE,
ABS(T2.COLS - T3.COLS) AS DISTANCE
FROM TS AS T2
INNER JOIN TS AS T3
ON T2.object_id = T3.object_id
AND T2.index_id <> T3.index_id
AND T2.KEY_ID_LIST LIKE T3.KEY_ID_LIST +'%'
)
-- résultat final
SELECT TX.*,
s.name +'.' + o.name AS NOM_TABLE,
i1.name AS NOM_INDEX,
i2.name AS NOM_INDEX_ANORMAL
, i1.filter_definition AS FILTRE_INDEX
, i2.filter_definition AS FILTRE_INDEX_ANORMAL
FROM TX
INNER JOIN sys.objects AS o
ON TX.object_id = o.object_id
INNER JOIN sys.schemas AS s
ON o.schema_id = s.schema_id
INNER JOIN sys.indexes AS i1
ON TX.object_id = i1.object_id
AND TX.index_id = i1.index_id
INNER JOIN sys.indexes AS i2
ON TX.object_id = i2.object_id
AND TX.index_id_anomalie = i2.index_id
WHERE o."type" IN ('U', 'V')
ORDER BY NOM_TABLE, NOM_INDEX; |
Partager