The query below lists all primary keys constraints (PK) in a database (schema).
See also: tables with their primary keys.
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;
- table_schema - PK database (schema) name
- pk_name - PK constraint name
- columns - list of PK columns separated by ','
- table_name - PK table name
- 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 for the Sakila database (schema)