Query below lists all primary keys constraints (PK) in the database with their columns (one row per column).

See also: list of all primary keys (one row per PK).

Query

select tab.tabschema as schema_name,
    const.constname as pk_name,
    key.colseq as position,
    key.colname as column_name,
    tab.tabname as table_name
from 
syscat.tables tab
inner join syscat.tabconst const 
    on const.tabschema = tab.tabschema 
    and const.tabname = tab.tabname and const.type = 'P'
inner join syscat.keycoluse key 
    on const.tabschema = key.tabschema 
    and const.tabname = key.tabname
    and const.constname = key.constname     
where tab.type = 'T'
    and tab.tabschema not like 'SYS%'
order by tab.tabschema, const.constname, key.colseq

Columns

• schema_name - PK schema name
• pk_name - PK constraint name
• position - id of column in index (1, 2, ...). 2 or higher means key is composite (contains more than one column)
• column_name - primary key column name
• table_name - PK table name

Rows

• One row represents one primary key column
• Scope of rows: columns of all PK constraints in a database
• Ordered by schema, PK constraint name, column id

Sample results

You could also get this

Get this interactive HTML data dictionary in minutes with Dataedo.

See live HTML data dictionary sample

Try for free