Declare @objectID int = Object_ID('Sales.SalesOrderHeader');
With indexCTE(partition_scheme_name
, partition_function_name
, data_space_id)
As (
Select sps.name
, spf.name
, sps.data_space_id
From sys.partition_schemes As sps
Join sys.partition_functions As spf
On sps.function_id = spf.function_id
)
Select st.name As 'table_name'
, IsNull(ix.name, '') As 'index_name'
, ix.object_id
, ix.index_id
, Cast(
Case When ix.index_id = 1
Then 'clustered'
When ix.index_id =0
Then 'heap'
Else 'nonclustered' End
+ Case When ix.ignore_dup_key <> 0
Then ', ignore duplicate keys'
Else '' End
+ Case When ix.is_unique <> 0
Then ', unique'
Else '' End
+ Case When ix.is_primary_key <> 0
Then ', primary key' Else '' End As varchar(210)
) As 'index_description'
, IsNull(Replace( Replace( Replace(
(
Select c.name As 'columnName'
From sys.index_columns As sic
Join sys.columns As c
On c.column_id = sic.column_id
And c.object_id = sic.object_id
Where sic.object_id = ix.object_id
And sic.index_id = ix.index_id
And is_included_column = 0
Order By sic.index_column_id
For XML Raw)
, '"/><row columnName="', ', ')
, '<row columnName="', '')
, '"/>', ''), '')
As 'indexed_columns'
, IsNull(Replace( Replace( Replace(
(
Select c.name As 'columnName'
From sys.index_columns As sic
Join sys.columns As c
On c.column_id = sic.column_id
And c.object_id = sic.object_id
Where sic.object_id = ix.object_id
And sic.index_id = ix.index_id
And is_included_column = 1
Order By sic.index_column_id
For XML Raw)
, '"/><row columnName="', ', ')
, '<row columnName="', '')
, '"/>', ''), '')
As 'included_columns'
, ix.filter_definition
, IsNull(cte.partition_scheme_name, '') As 'partition_scheme_name'
, Count(partition_number) As 'partition_count'
, Sum(rows) As 'row_count'
From sys.indexes As ix
Join sys.partitions As sp
On ix.object_id = sp.object_id
And ix.index_id = sp.index_id
Join sys.tables As st
On ix.object_id = st.object_id
Left Join indexCTE As cte
On ix.data_space_id = cte.data_space_id
Where ix.object_id = IsNull(@objectID, ix.object_id)
Group By st.name
, IsNull(ix.name, '')
, ix.object_id
, ix.index_id
, Cast(
Case When ix.index_id = 1
Then 'clustered'
When ix.index_id =0
Then 'heap'
Else 'nonclustered' End
+ Case When ix.ignore_dup_key <> 0
Then ', ignore duplicate keys'
Else '' End
+ Case When ix.is_unique <> 0
Then ', unique'
Else '' End
+ Case When ix.is_primary_key <> 0
Then ', primary key' Else '' End As varchar(210)
)
, ix.filter_definition
, IsNull(cte.partition_scheme_name, '')
, IsNull(cte.partition_function_name, '')
Order By table_name
, index_id; |
