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; |
It will be helpful if you can add Index Fragmentation column to show the average fragmentation percentage & size while interrogating those Indexes.
thanks
Jay
Hi Jay. The current query above just uses meta data to return information about indexes. Because of that, it’s very fast and lightweight. Fragmentation information, on the other hand, would be a very expensive operation, as it examines the actual page data to determine the amount of fragmentation. I’d much prefer to keep the index information script lightweight, since I usually just want to see columns are part of the index, etc. If you’re looking for fragmentation information, you can query the sys.dm_db_index_physical_stats DMF or you can use my index defrag script here: http://sqlfool.com/2010/04/index-defrag-script-v4-0/
Thanks Michelle! Another cool script. Sadly I have yet to find the script that gets the indexed views information, especially when we start hitting the include columns. One of these days I’ll have to sit down and write something. 🙂
Thanks again.