List all primary keys (PKs) in Db2 database

Query below lists all primary keys constraints (PK) in the database.

See also: tables with their primary keys.

Query

select tab.tabschema as schema_name,
    const.constname as pk_name,
    listagg(key.colname, ', ') 
            within group(order by key.colseq) as columns,
    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'
 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%'
group by tab.tabschema, const.constname, tab.tabname   
order by tab.tabschema, const.constname

Columns

  • schema_name - PK schema name
  • pk_name - PK constraint name
  • columns - list of PK columns separated with ','
  • table_name - PK table name

Rows

  • One row represents one primary key (table) in a database
  • Scope of rows: all PK constraints in a database
  • Ordered by schema, PK constraint name

Sample results