How to get clustered index information with DMV in SQL Server 2005
How to get clustered index information with DMV in SQL Server 2005
Written by : Kasim Wirama, MCDBA, MVP SQL Server
I would like to share my code to query clustered index information for a table in SQL Server 2005 by leveraging dynamic management view (DMV).
You just supply table name dan it results clustered index name together with its columns.
declare @tablename sysname;
Set @tablename = 'HumanResources.Employee';
@tablename sysname;
Set @tablename = 'HumanResources.Employee';with cte (index_name,column_name, index_type) as
(
cte (index_name,column_name, index_type) as
(select i.name , c.name , case when i.index_id = 1 then 'clustered' else 'non clustered' end
select i.name , c.name , case when i.index_id = 1 then 'clustered' else 'non clustered' endfrom sys.index_columns ic join sys.columns con ic.object_id = c.object_id
from sys.index_columns ic join sys.columns con ic.object_id = c.object_id
on ic.object_id = c.object_idand ic.column_id = c.column_idjoin sys.indexes ion i.object_id = ic.object_id
and ic.column_id = c.column_idjoin sys.indexes ion i.object_id = ic.object_id
join sys.indexes ion i.object_id = ic.object_id
on i.object_id = ic.object_idand i.index_id = ic.index_idwhere i.index_id>0 and i.object_id=object_id(@tablename) ),
and i.index_id = ic.index_idwhere i.index_id>0 and i.object_id=object_id(@tablename) ),
where i.index_id>0 and i.object_id=object_id(@tablename) ),
),cte1 (index_name_cte1) as (
(index_name_cte1) as (
(select index_namefrom ctewhere column_name in ('')
select index_namefrom ctewhere column_name in ('')
from ctewhere column_name in ('')
where column_name in ('')group by index_name),
group by index_name),
),cte2 (index_name_include) as
(index_name_include) as(
(select index_namefrom cteexcept
select index_namefrom cteexcept
from cteexcept
exceptselect index_name_cte1from cte1)
select index_name_cte1from cte1)
from cte1)
)select index_name_include,column_name from (
select index_name_include,column_name from (
(select cte2.index_name_include, (select top 1 index_type from cte where index_name = cte2.index_name_include ) as index_type from cte2) as D1 join cte on D1.index_name_include = cte.index_namewhere D1.index_type = 'clustered';
select cte2.index_name_include, (select top 1 index_type from cte where index_name = cte2.index_name_include ) as index_type from cte2) as D1 join cte on D1.index_name_include = cte.index_namewhere D1.index_type = 'clustered';
) as D1 join cte on D1.index_name_include = cte.index_namewhere D1.index_type = 'clustered';
where D1.index_type = 'clustered';