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 as table_name
, as index_name
, ic.index_column_id — column order in the index
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
, 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: Logo

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

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.