The query below lists all primary keys constraints (PK) in a database (schema).

See also: tables with their primary keys.

Query

select tab.table_schema as database_name,
    sta.index_name as pk_name,
    group_concat(distinct sta.column_name order by sta.column_name) as 'columns',
    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'
group by table_name
order by table_name;

Columns

• table_schema - PK database (schema) name
• pk_name - PK constraint name
• columns - list of PK columns separated by ','
• table_name - PK table name

Rows

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

Sample results

Sample results for the Sakila database (schema)