Navigation

	Home Page
	Index
	All Pages

Contributor Links

	Create a new Page
	Administration
	File Management
	Login/Logout
	Your Profile

Other Wiki Sections

Software

Index Columns - SQL Server

select
     IndexName = i.name
    ,TableName = o.name
    ,ColumnName = c.name
    
from
    sys.indexes i

    inner join sys.objects o
        on i.object_id = o.object_id

    inner join sys.index_columns ic
        on i.index_id = ic.index_id

    inner join sys.columns c
        on ic.column_id = c.column_id
        and c.object_ID = O.OBJECT_ID

where 1=1
    and o.type = 'u'

select
     TableName = o.name
    ,IndexName = idx.name
    ,ColumnNames = string_agg(case when ic.is_included_column=1 then '(' + c.name + ')' else c.name end, ',') within group (order by ic.index_column_id)
from
    sys.indexes idx
    inner join sys.objects o 
        on idx.object_id = o.object_id
    inner join sys.index_columns ic
        on idx.index_id = ic.index_id
    inner join sys.columns c
        on ic.column_id = c.column_id
        and o.object_id = c.object_id
where 1=1
    and o.type = 'U'
    /* This condition limits our results to indexes recommended by Azure */
    and idx.name like 'nci%'
group by
     o.name
    ,idx.name