Monday, 20 June 2011

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

Inspired by a Stack Overflow post:

select as [Table Name], as [Column Name], as [Index Name]
    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
    ind.is_primary_key = 0
    and ind.is_unique = 0
    and ind.is_unique_constraint = 0
    and t.is_ms_shipped = 0
order by,, ind.index_id, ic.index_column_id