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 tco.table_schema as database_name,
       tco.constraint_name as pk_name,
       kcu.ordinal_position as column_id,
       kcu.column_name,
       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
order by tco.table_schema,
         tco.table_name,
         kcu.ordinal_position;

Columns

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