Listing all indexes

Question

I'm wondering what the simplest way to list all indexes for all tables in a database is.

Should I call sp_helpindex for each table and store the results in a temp table, or is there an easier way?

Can anyone explain why constraints are stored in sysobjects but indexes are not?

Answer 1

Here's an example of the kind of query you need:

select 
    i.name as IndexName, 
    o.name as TableName, 
    ic.key_ordinal as ColumnOrder,
    ic.is_included_column as IsIncluded, 
    co.[name] as ColumnName
from sys.indexes i 
join sys.objects o on i.object_id = o.object_id
join sys.index_columns ic on ic.object_id = i.object_id 
    and ic.index_id = i.index_id
join sys.columns co on co.object_id = i.object_id 
    and co.column_id = ic.column_id
where i.[type] = 2 
and i.is_unique = 0 
and i.is_primary_key = 0
and o.[type] = 'U'
--and ic.is_included_column = 0
order by o.[name], i.[name], ic.is_included_column, ic.key_ordinal
;

This one is somewhat specific to a certain purpose (I use it in a little C# app to find duplicate indexes and format the output so it's actually readable by a human). But you could easily adapt it to your needs.