The query below lists all primary keys constraints (PK) in a database (schema) with their columns (one row per column).

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

Query

select tab.table_schema as database_schema,
    sta.index_name as pk_name,
    sta.seq_in_index as column_id,
    sta.column_name,
    tab.table_name
from information_schema.tables as tab
inner join information_schema.statistics as sta
        on sta.table_schema = tab.table_schema
        and sta.table_name = tab.table_name
        and sta.index_name = 'primary'
where tab.table_schema = 'your database name'
    and tab.table_type = 'BASE TABLE'
order by tab.table_name,
    column_id;

Columns

• table_schema - PK database (schema) name
• pk_name - PK constraint name
• column_id - id of the column in index (1, 2, ...). 2 or higher means the 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 (schema)
• Ordered by: table name, column id

Sample results

Sample results for the Sakila database (schema)

You could also get this

Get this interactive HTML data dictionary in minutes with Dataedo.

See live HTML data dictionary sample

Try for free