78 lines
4.0 KiB
SQL
78 lines
4.0 KiB
SQL
|
|
WITH
|
|
-- sous requête CTE donnant les index avec leurs colonnes
|
|
T0 AS (SELECT ic.object_id, index_id, c.column_id, key_ordinal,
|
|
CASE is_descending_key
|
|
WHEN '0' THEN 'ASC'
|
|
WHEN '1' THEN 'DESC'
|
|
END AS sens, c.name AS column_name,
|
|
ROW_NUMBER() OVER(PARTITION BY ic.object_id, index_id ORDER BY key_ordinal DESC) AS N,
|
|
is_included_column
|
|
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 key_ordinal > 0
|
|
AND index_id > 0),
|
|
-- sous requête CTE récursive composant les clefs des index sous forme algébrique et littérale
|
|
T1 AS (SELECT object_id, index_id, column_id, key_ordinal, N,
|
|
CASE WHEN is_included_column = 0 THEN CAST(column_name AS VARCHAR(MAX)) + ' ' + sens ELSE '' END AS COMP_LITTERALE,
|
|
CASE WHEN is_included_column = 0 THEN CAST(column_id AS VARCHAR(MAX)) + SUBSTRING(sens, 1, 1) ELSE '' END AS COMP_MATH,
|
|
MAX(N) OVER(PARTITION BY object_id, index_id) AS CMAX,
|
|
CASE WHEN is_included_column = 1 THEN CAST(column_name AS VARCHAR(MAX)) ELSE '' END AS COLONNES_INCLUSES
|
|
FROM T0
|
|
WHERE key_ordinal = 1
|
|
UNION ALL
|
|
SELECT T0.object_id, T0.index_id, T0.column_id, T0.key_ordinal, T0.N,
|
|
COMP_LITTERALE +
|
|
CASE WHEN is_included_column = 0 THEN ', ' + CAST(T0.column_name AS VARCHAR(MAX)) + ' ' + T0.sens ELSE '' END,
|
|
COMP_MATH +
|
|
CASE WHEN is_included_column = 0 THEN CAST(T0.column_id AS VARCHAR(MAX)) + SUBSTRING(T0.sens, 1, 1) ELSE '' END,
|
|
T1.CMAX, COLONNES_INCLUSES + CASE WHEN is_included_column = 1 THEN ', ' + CAST(column_name AS VARCHAR(MAX)) ELSE '' END
|
|
FROM T0
|
|
INNER JOIN T1
|
|
ON T0.object_id = T1.object_id
|
|
AND T0.index_id = T1.index_id
|
|
AND T0.key_ordinal = T1.key_ordinal + 1),
|
|
-- sous requête CTE de dédoublonnage
|
|
T2 AS (SELECT object_id, index_id, COMP_LITTERALE, COMP_MATH, CMAX, COLONNES_INCLUSES
|
|
FROM T1
|
|
WHERE N = 1),
|
|
-- sous requête sélectionnant les anomalies
|
|
T4 AS (SELECT T2.object_id, T2.index_id,
|
|
T3.index_id AS index_id_anomalie,
|
|
T2.COMP_LITTERALE AS CLEF_INDEX,
|
|
T3.COMP_LITTERALE AS CLEF_INDEX_ANORMAL,
|
|
T2.COLONNES_INCLUSES, T3.COLONNES_INCLUSES AS COLONNES_INCLUSES_ANORMAL,
|
|
CASE
|
|
WHEN T2.COMP_MATH = T3.COMP_MATH
|
|
THEN 'DOUBLONS'
|
|
WHEN T2.COMP_MATH LIKE T3.COMP_MATH +'%'
|
|
THEN 'INCLUS'
|
|
END AS ANOMALIE,
|
|
ABS(T2.CMAX - T3.CMAX) AS DISTANCE
|
|
FROM T2
|
|
INNER JOIN T2 AS T3
|
|
ON T2.object_id = T3.object_id
|
|
AND T2.index_id <> T3.index_id
|
|
AND T2.COMP_MATH LIKE T3.COMP_MATH +'%')
|
|
-- Requête finale rajoutant les informations manquantes
|
|
SELECT T4.*,
|
|
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 T4
|
|
INNER JOIN sys.objects AS o
|
|
ON T4.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 T4.object_id = i1.object_id
|
|
AND T4.index_id = i1.index_id
|
|
INNER JOIN sys.indexes AS i2
|
|
ON T4.object_id = i2.object_id
|
|
AND T4.index_id_anomalie = i2.index_id
|
|
WHERE o."type" IN ('U', 'V')
|
|
ORDER BY NOM_TABLE, NOM_INDEX; |