SQL Server : lister les index, leurs colonnes et leur type
Lister les index des objets utilisateur avec leurs colonnes clés, leur type et leur caractère unique.
Cette requête liste les index des objets utilisateur, avec leurs colonnes clés (dans l'ordre de l'index), leur type, leur caractère unique et le nom de la table ou de la vue. Les colonnes sont agrégées par FOR XML PATH ; la virgule finale est retirée.
Requête
select i.[name] as index_name,
substring(column_names, 1, len(column_names)-1) as [columns],
case when i.[type] = 1 then 'Clustered index'
when i.[type] = 2 then 'Nonclustered unique index'
when i.[type] = 3 then 'XML index'
when i.[type] = 4 then 'Spatial index'
when i.[type] = 5 then 'Clustered columnstore index'
when i.[type] = 6 then 'Nonclustered columnstore index'
when i.[type] = 7 then 'Nonclustered hash index'
end as index_type,
case when i.is_unique = 1 then 'Unique'
else 'Not unique' end as [unique],
schema_name(t.schema_id) + '.' + t.[name] as table_view,
case when t.[type] = 'U' then 'Table'
when t.[type] = 'V' then 'View'
end as [object_type]
from sys.objects t
inner join sys.indexes i
on t.object_id = i.object_id
cross apply (select col.[name] + ', '
from sys.index_columns ic
inner join sys.columns col
on ic.object_id = col.object_id
and ic.column_id = col.column_id
where ic.object_id = t.object_id
and ic.index_id = i.index_id
order by key_ordinal
for xml path ('') ) D (column_names)
where t.is_ms_shipped <> 1 --and i.[name] like '%IndexNamePattern%'
and index_id > 0
order by i.[name]
Pour retrouver des index par nom :
select * from sys.indexes
where name like '%IndexNamePattern%'
Remarques
- Les heaps (
index_id = 0) sont exclus. - Seuls les types
U(table) etV(vue) sont libellés ; les autres objets affichentNULLdansobject_type. - Les colonnes incluses (
INCLUDE) ne figurent pas dans la liste. - Voir aussi l'article Microsoft Cumulative Update 8 for SQL Server 2019.