LLI Wiki — My Digital Knowledge Base

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

Mastodon LLIMastodon SocialFosstodon