List all primary keys (PKs) in MySQL database

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)

0
There are no comments. Click here to write the first comment.