|
1
|
select i.[name] as index_name,
|
|
2
|
substring(column_names, 1, len(column_names)-1) as [columns],
|
|
3
|
case when i.[type] = 1 then 'Clustered index'
|
|
4
|
when i.[type] = 2 then 'Nonclustered unique index'
|
|
5
|
when i.[type] = 3 then 'XML index'
|
|
6
|
when i.[type] = 4 then 'Spatial index'
|
|
7
|
when i.[type] = 5 then 'Clustered columnstore index'
|
|
8
|
when i.[type] = 6 then 'Nonclustered columnstore index'
|
|
9
|
when i.[type] = 7 then 'Nonclustered hash index'
|
|
10
|
end as index_type,
|
|
11
|
case when i.is_unique = 1 then 'Unique'
|
|
12
|
else 'Not unique' end as [unique],
|
|
13
|
schema_name(t.schema_id) + '.' + t.[name] as table_view,
|
|
14
|
case when t.[type] = 'U' then 'Table'
|
|
15
|
when t.[type] = 'V' then 'View'
|
|
16
|
end as [object_type]
|
|
17
|
from sys.objects t
|
|
18
|
inner join sys.indexes i
|
|
19
|
on t.object_id = i.object_id
|
|
20
|
cross apply (select col.[name] + ', '
|
|
21
|
from sys.index_columns ic
|
|
22
|
inner join sys.columns col
|
|
23
|
on ic.object_id = col.object_id
|
|
24
|
and ic.column_id = col.column_id
|
|
25
|
where ic.object_id = t.object_id
|
|
26
|
and ic.index_id = i.index_id
|
|
27
|
order by key_ordinal
|
|
28
|
for xml path ('') ) D (column_names)
|
|
29
|
where t.is_ms_shipped <> 1
|
|
30
|
and index_id > 0
|
|
31
|
order by i.[name]
|