Jasinski Technical Wiki

Navigation

Home Page
Index
All Pages

Quick Search
»
Advanced Search »

Contributor Links

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

Other Wiki Sections

Software

PoweredBy

Index Columns - SQL Server

RSS
Modified on Wed, Feb 22, 2023, 8:27 AM by Administrator Categorized as SQL Server, SQL Server System Tables
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

ScrewTurn Wiki version 3.0.1.400. Some of the icons created by FamFamFam. Except where noted, all contents Copyright © 1999-2024, Patrick Jasinski.