SQL Server: list indexes with their columns and type
List the indexes of the user objects with their key columns, type and uniqueness.
This query lists the indexes of the user objects, with their key columns (in index order), their type, whether they are unique, and the name of the table or view. The columns are aggregated with FOR XML PATH; the trailing comma is removed.
Query
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]
To find indexes by name:
select * from sys.indexes
where name like '%IndexNamePattern%'
Notes
- Heaps (
index_id = 0) are excluded. - Only the
U(table) andV(view) object types get a label; other objects showNULLinobject_type. - Included columns (
INCLUDE) are not listed. - See also the Microsoft article Cumulative Update 8 for SQL Server 2019.