TSQL to get clustered index information in SQL Server 2005

Recently within a supportal case with CSS we have been given the following TSQL to get information on indexes, where I have modified a bit to get 'Clustered' index information alone that was helpful to  see which tables lack of clustered index alone.

declare @tablename sysname;
Set @tablename = '<TableName>';

@tablename sysname;
Set @tablename = '<TableName';

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' end

from sys.index_columns ic join sys.columns c on ic.object_id = c.object_id

 

from sys.index_columns ic join sys.columns c on ic.object_id = c.object_id on ic.object_id = c.object_id and ic.column_id = c.column_id

join sys.indexes i on i.object_id = ic.object_id and ic.column_id = c.column_id

join sys.indexes i on i.object_id = ic.object_id

 

join sys.indexes i on i.object_id = ic.object_idon i.object_id = ic.object_id and i.index_id = ic.index_id

where i.index_id>0 and i.object_id=object_id(@tablename) ), and i.index_id = ic.index_id

where 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_name from cte where column_name in ('')

select index_name from cte where column_name in ('')

from cte where column_name in ('') group by index_name ),

 

group by index_name ),), cte2 (index_name_include) as(index_name_include) as

(( select index_name from cte except

 

select index_name from cte except

 

from cte except except

select index_name_cte1 from cte1 )

 

select index_name_cte1 from 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_name where 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_name where D1.index_type = 'clustered';

 

) as D1 join cte on D1.index_name_include = cte.index_name where D1.index_type = 'clustered'; where D1.index_type = 'clustered';