SQL Server : Listing Indexed Columns for a Table

Problem

You want list indexes, their columns, and the column position (if available) in the index for a given table.

Solution

The vendor-specific solutions that follow all assume that you are listing indexes for the table EMP in the SMEAGOL schema.

DB2

Query SYSCAT.INDEXES:

 1  select a.tabname, b.indname, b.colname, b.colseq
 2    from syscat.indexes a,
 3         syscat.indexcoluse b
 3   where a.tabname   = 'EMP'
 4     and a.tabschema = 'SMEAGOL'
 5     and a.indschema = b.indschema
 6     and a.indname   = b.indname

Oracle

Query SYS.ALL_IND_COLUMNS:

 select table_name, index_name, column_name, column_position
   from sys.all_ind_columns
  where table_name  = 'EMP'
    and table_owner = 'SMEAGOL'

PostgreSQL

Query PG_CATALOG.PG_INDEXES and INFORMATION_SCHEMA.COLUMNS:

 1  select a.tablename,a.indexname,b.column_name
 2    from pg_catalog.pg_indexes a,
 3         information_schema.columns b
 4   where a.schemaname = 'SMEAGOL'
 5     and a.tablename  = b.table_name

MySQL

Use the SHOW INDEX command:

 show index from emp

SQL Server

Query SYS.TABLES, SYS.INDEXES, SYS.INDEX_COLUMNS, and SYS.COLUMNS:

  1  select a.name table_name,
  2         b.name index_name,
  3         d.name column_name,
  4         c.index_column_id
  5    from sys.tables a,
  6         sys.indexes b,
  7         sys.index_columns c,
  8         sys.columns d
  9  where a.object_id = b.object_id
 10    and b.object_id = c.object_id
 11    and b.index_id  = c.index_id
 12    and c.object_id = d.object_id
 13    and c.column_id = d.column_id
 14    and a.name      = 'EMP'

Discussion

When it comes to queries, it's important to know what columns are/aren't indexed. Indexes can provide good performance for queries against columns that are frequently used in filters and that are fairly selective. Indexes are also useful when joining between tables. By knowing what columns are indexed, you are already one step ahead of performance problems if they should occur. Additionally, you might want to find information about the indexes themselves: how many levels deep they are, how many distinct keys, how many leaf blocks, and so forth. Such information is also available from the views/tables queried in this recipe's solutions.

** If you want the Full Table detail. Refer the SQL Table in Label List. Or Click here to View the Table