How to list indexes and the column names?

— Lists user indexes that are not primary and not unique indexes in SQLServer 2005 and up
select t.name as table_name
, ind.name as index_name
, ic.index_column_id — column order in the index
, col.name
from 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
where ind.is_primary_key = 0
and ind.is_unique = 0
and ind.is_unique_constraint = 0
and t.is_ms_shipped = 0
order by t.name
, ind.index_id
, ic.index_column_id

table_name index_name index_column_id column_name
————— ————— —————— —————-
base_action PK_base_action 1 action_id
base_category PK_base_category 1 type_id
base_category PK_base_category 2 table_id

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s