Monday 20 June 2011

Get name, table and column of all indexes in a SQL Server database

Inspired by a Stack Overflow post:

select
t.name as [Table Name], col.name as [Column Name], ind.name as [Index Name]
from
    sys.indexes ind
inner join
    sys.index_columns ic on
      ind.object_id = ic.object_id and ind.index_id = ic.index_id
inner join
    sys.columns col on
      ic.object_id = col.object_id and ic.column_id = col.column_id
inner join
    sys.tables t on
      ind.object_id = t.object_id
where
    ind.is_primary_key = 0
    and ind.is_unique = 0
    and ind.is_unique_constraint = 0
    and t.is_ms_shipped = 0
order by
    t.name, ind.name, ind.index_id, ic.index_column_id