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.