Lister les caractéristiques des indexes sous SQL Server 2005 et 2008

Publié le par

Voici une requête qui nous permet de retrouver pour tout index :

Рla liste de ses colonnes cl̩
– la liste de ses colonnes incluses
Рla d̩finition de son filtre
Рle script de cr̩ation de cet index

Voici une première requête qui retourne la définition de tous les index d’une base de données.
On peut bien sûr ajouter des filtres afin de voir la définition des index sur une table, ou la définition d’un index en particulier.

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
-------------------------------
-- Nicolas Souquet - 22/02/2011
-------------------------------
SELECT    DISTINCT S.name AS schema_name
    , T.name AS table_name
    , I.name AS index_name
    , LEFT(KC.key_column_list, LEN(KC.key_column_list) - 1) AS key_column_list
    , LEFT(KCI.included_key_column_list, LEN(KCI.included_key_column_list) - 1) AS included_key_column_list
    , REPLACE(REPLACE(REPLACE(REPLACE(I.filter_definition, '[', ''), ']', ''), '(', ''), ')', '') AS filter_definition
FROM    sys.schemas AS S
INNER JOIN  sys.tables AS T
      ON S.schema_id = T.schema_id
INNER JOIN  sys.indexes AS I  
      ON T.object_id = I.object_id
INNER JOIN  sys.index_columns AS IC
      ON IC.object_id = I.object_id
      AND IC.index_id = I.index_id
INNER JOIN  sys.columns AS C
      ON IC.object_id = C.object_id
      AND IC.column_id = C.column_id
CROSS APPLY  (
      SELECT    CS.name + ','
      FROM    sys.columns AS CS
      INNER JOIN  sys.index_columns AS ICS
            ON CS.object_id = ICS.object_id
            AND CS.column_id = ICS.column_id
      WHERE    IC.object_id = ICS.object_id
      AND    IC.index_id = ICS.index_id
      AND    ICS.is_included_column = 0
      ORDER BY  ICS.index_column_id
      FOR XML PATH ('')
    ) AS KC (key_column_list)
OUTER APPLY  (
      SELECT    CSI.name + ','
      FROM    sys.columns AS CSI
      INNER JOIN  sys.index_columns AS ICSI
            ON CSI.object_id = ICSI.object_id
            AND CSI.column_id = ICSI.column_id
      WHERE    IC.object_id = ICSI.object_id
      AND    IC.index_id = ICSI.index_id
      AND    ICSI.is_included_column = 1
      ORDER BY  ICSI.index_column_id
      FOR XML PATH ('')
    ) AS KCI (included_key_column_list)
WHERE    S.name = 'dbo'
--AND    T.name = 'maTable'
--AND    I.name = 'monIndex'
ORDER BY  T.name, I.name

Et une autre donnant le script de définition de l’index :

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
-------------------------------
-- Nicolas Souquet - 22/02/2011
-------------------------------
;WITH
  CTE AS
  (
    SELECT    DISTINCT S.name AS schema_name
        , T.name AS table_name
        , I.name AS index_name
        , LEFT(KC.key_column_list, LEN(KC.key_column_list) - 1) AS key_column_list
        , LEFT(KCI.included_key_column_list, LEN(KCI.included_key_column_list) - 1) AS included_key_column_list
        , REPLACE(REPLACE(REPLACE(REPLACE(I.filter_definition, '[', ''), ']', ''), '(', ''), ')', '') AS filter_definition
        , ROW_NUMBER() OVER(ORDER BY T.name, I.name) AS n
    FROM    sys.schemas AS S
    INNER JOIN  sys.tables AS T
          ON S.schema_id = T.schema_id
    INNER JOIN  sys.indexes AS I  
          ON T.object_id = I.object_id
    INNER JOIN  sys.index_columns AS IC
          ON IC.object_id = I.object_id
          AND IC.index_id = I.index_id
    INNER JOIN  sys.columns AS C
          ON IC.object_id = C.object_id
          AND IC.column_id = C.column_id
    CROSS APPLY  (
          SELECT    CS.name + ','
          FROM    sys.columns AS CS
          INNER JOIN  sys.index_columns AS ICS
                ON CS.object_id = ICS.object_id
                AND CS.column_id = ICS.column_id
          WHERE    IC.object_id = ICS.object_id
          AND    IC.index_id = ICS.index_id
          AND    ICS.is_included_column = 0
          ORDER BY  ICS.index_column_id
          FOR XML PATH ('')
        ) AS KC (key_column_list)
    OUTER APPLY  (
          SELECT    CSI.name + ','
          FROM    sys.columns AS CSI
          INNER JOIN  sys.index_columns AS ICSI
                ON CSI.object_id = ICSI.object_id
                AND CSI.column_id = ICSI.column_id
          WHERE    IC.object_id = ICSI.object_id
          AND    IC.index_id = ICSI.index_id
          AND    ICSI.is_included_column = 1
          ORDER BY  ICSI.index_column_id
          FOR XML PATH ('')
        ) AS KCI (included_key_column_list)
    WHERE    S.name = 'dbo'
    --AND    T.name = 'maTable'
    --AND    I.name = 'monIndex'
  )
SELECT  'CREATE INDEX ' + index_name  
  + ' ON ' + schema_name + '.' + table_name
  + '(' + key_column_list + ')'
  + CASE WHEN included_key_column_list IS NOT NULL THEN ' INCLUDE(' + included_key_column_list + ')' ELSE '' END
  + CASE WHEN filter_definition IS NOT NULL THEN ' WHERE ' + filter_definition ELSE '' END
FROM  CTE

Laisser un commentaire