Query below lists tables and their primary key (PK) constraint names. By browsing list you can spot which tables have and which don't have primary keys.

See also: tables without primary keys.

Query

select tab.tabschema as schema_name,
    tab.tabname as table_name,
    const.constname as pk_name,
    listagg(key.colname, ', ') 
            within group(order by key.colseq) as column_name
from 
syscat.tables tab
left outer join syscat.tabconst const 
    on const.tabschema = tab.tabschema 
    and const.tabname = tab.tabname and const.type = 'P'
left outer 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, tab.tabname

Columns

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

Rows

• One row represents one table in a database
• Scope of rows: all tables in a database
• Ordered by schema, table name

Sample results

You can see what are the names of PK constraints for each table and which tables don't have PKs at all (in SAMPLE database).

You could also get this

Get this interactive HTML data dictionary in minutes with Dataedo.

See live HTML data dictionary sample

Try for free