Recipe5.3.Listing Indexed Columns for a Table | SQL Cookbook (Cookbooks (OReilly))

Recipe 5.3. 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.