Voici une petite requête qui permet de lister les colonnes de tous les index d’une base de données, avec leur type et l’ordre des colonnes dans la clé de l’index :
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | ------------------------------- -- Nicolas SOUQUET - 08/03/2010 ------------------------------- -- SQL Server 2005 et 2008 SELECT T.name AS nom_table , I.type_desc AS type_index , I.name AS nom_index , C.name AS nom_colonne , IC.key_ordinal AS position_colonne , IC.is_included_column AS is_included FROM sys.tables AS T INNER JOIN sys.indexes AS I ON T.object_id = I.object_id INNER JOIN sys.index_columns AS IC ON I.object_id = IC.object_id AND I.index_id = IC.index_id INNER JOIN sys.columns AS C ON IC.object_id = C.object_id AND IC.column_id = C.column_id --WHERE T.name = 'maTable' ORDER BY T.name, I.name, IC.key_ordinal |
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 | ------------------------------- -- Nicolas SOUQUET - 20/07/2010 ------------------------------- -- SQL Server 2000 SELECT T.name AS table_name , I.name AS index_name , CASE I.indid WHEN 1 THEN 'CLUSTER' ELSE 'NON-CLUSTER' END AS index_type , C.name AS column_name , IK.keyno AS index_key_col_pos FROM sysindexes AS I INNER JOIN sysobjects AS T ON I.id = T.id INNER JOIN sysindexkeys AS IK ON I.id = IK.id AND I.indid = IK.indid INNER JOIN syscolumns AS C ON IK.id = C.id AND IK.colid = C.colid WHERE T.type = 'U' AND I.name NOT LIKE '%_!WA!_Sys!_%' ESCAPE '!' AND I.indid > 0 ORDER BY T.name, I.name |
Bonne indexation !
ElSuket
Bonjour BullsEye,
Je viens de copier-coller la requête dans SSMS 2012 et de l’exécuter sur AdventureWorks2012 dans une instance SQL Server 2012 SP1, et la requête fonctionne correctement. Peux-tu décrire le problème que tu as eu plus précisément ?
@++
C’est presque bon, il y a des AS en trop dans la version 2005/2008.
Au final, ça doit donner :
SELECT T.name AS nom_table
, I.type_desc AS type_index
, I.name AS nom_index
, C.name AS nom_colonne
, IC.key_ordinal AS position_colonne
, IC.is_included_column AS is_included
FROM sys.tables T
INNER JOIN sys.indexes I
ON T.object_id = I.object_id
INNER JOIN sys.index_columns IC
ON I.object_id = IC.object_id
AND I.index_id = IC.index_id
INNER JOIN sys.columns C
ON IC.object_id = C.object_id
AND IC.column_id = C.column_id
–WHERE T.name = ‘maTable’
ORDER BY T.name, I.name, IC.key_ordinal