Metadata Queries: Listing Indexed Columns for a Table
DB2
Query SYSCAT.INDEXES:
select a.tabname, b.indname, b.colname, b.colseq from syscat.indexes a, syscat.indexcoluse b
where a.tabname = 'EMP'
and a.tabschema = 'SMEAGOL'
and a.indschema = b.indschema
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:
select a.tablename,a.indexname,b.column_name from pg_catalog.pg_indexes a, information_schema.columns b
where a.schemaname = 'SMEAGOL' 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:
select a.name table_name, b.name index_name, d.name column_name, c.index_column_id
from sys.tables a, sys.indexes b, sys.index_columns c, sys.columns d
where a.object_id = b.object_id
and b.object_id = c.object_id
and b.index_id = c.index_id
and c.object_id = d.object_id
and c.column_id = d.column_id
and a.name = 'EMP'
posted by kanun # 6:19 AM 
