How do I get a list of all index & index columns in SQL Server 2005+? The closest I could get is:
select s.name, t.name, i.name, c.name from sys.tables t
inner join sys.schemas s on t.schema_id = s.schema_id
inner join sys.indexes i on i.object_id = t.object_id
inner join sys.index_columns ic on ic.object_id = t.object_id
inner join sys.columns c on c.object_id = t.object_id and
ic.column_id = c.column_id
where i.index_id > 0
and i.type in (1, 2) -- clustered & nonclustered only
and i.is_primary_key = 0 -- do not include PK indexes
and i.is_unique_constraint = 0 -- do not include UQ
and i.is_disabled = 0
and i.is_hypothetical = 0
and ic.key_ordinal > 0
order by ic.key_ordinal
Which is not exactly what I want.
What I want is, to list all user-defined indexes, (which means no indexes which support unique constraints & primary keys) with all columns (ordered by how do they appear in index definition) plus as much metadata as possible.
Best Answer
There are two "sys" catalog views you can consult:
sys.indexes
andsys.index_columns
.Those will give you just about any info you could possibly want about indices and their columns.
EDIT: This query's getting pretty close to what you're looking for: