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

See also: tables with their primary keys.

Query

select tco.table_schema as database_name,
       tco.constraint_name as pk_name,
       group_concat(kcu.column_name
            order by kcu.ordinal_position
            separator ', ') as columns,
       tco.table_name
from information_schema.table_constraints tco
join information_schema.key_column_usage kcu
     on tco.constraint_schema = kcu.constraint_schema
     and tco.constraint_name = kcu.constraint_name
     and tco.table_name = kcu.table_name
where tco.constraint_type = 'PRIMARY KEY'
      and tco.table_schema not in  ('sys','information_schema',
                                    'mysql', 'performance_schema')
     -- and tco.table_schema  = 'database_name' -- put your database name here
group by tco.table_schema,
         tco.constraint_name,
         tco.table_name
order by tco.table_schema,
         tco.table_name;

Columns

• database_name - 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: database name, table name

Sample results

Sample results for the Sakila database (schema)