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

Query

select tco.constraint_schema,
       tco.constraint_name,
       kcu.ordinal_position as position,
       kcu.column_name as key_column,
       kcu.table_schema || '.' || kcu.table_name as table
from information_schema.table_constraints tco
join information_schema.key_column_usage kcu 
     on kcu.constraint_name = tco.constraint_name
     and kcu.constraint_schema = tco.constraint_schema
     and kcu.constraint_name = tco.constraint_name
where tco.constraint_type = 'PRIMARY KEY'
order by tco.constraint_schema,
         tco.constraint_name,
         kcu.ordinal_position;

Columns

• constraint_schema- PK constraint schema name
• constraint_name - PK constraint name
• position - index of column in table (1, 2, ...). 2 or higher means key is composite (contains more than one column)
• key_column - PK column name
• table - table schema name and tabl name on which PK is defined

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 position

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