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.
Since your profile states that you are using .NET you could use Server Managed Objects (SMO) programmatically... otherwise any of the above answers are fantastic.
The query below includes all of the pertinent information for the user-defined indexes, (no indexes for unique constraints & primary keys) with all columns:
As an added bonus, the below query is formatted to write out the create index and drop index scripts:
You can use the
sp_helpindex
to view all the indexes of one table.And for all the indexes, you can traverse
sys.objects
to get all the indexes for each table.--Short and sweet:
this will work:
this does not reurn the table name and you will get warnings for all tables without an index, if that is a problem, you can create a loop over the tables that have indexes like this:
EDIT
if you want, you can filter the data, here are some examples (these work for either method):
May I hazard another answer to this saturated question?
This is a liberal reworking of @marc_s answer, mixed with some stuff from @Tim Ford, with the goal of having a bit of a cleaner and simpler result set and final display and ordering for my current need.