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).
select tab.table_schema as database_schema, sta.index_name as pk_name, sta.seq_in_index as column_id, sta.column_name, 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' order by tab.table_name, column_id;
- table_schema - 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
- One row: represents one primary key column
- Scope of rows: columns of all PK constraints in a database (schema)
- Ordered by: table name, column id
Sample results for the Sakila database (schema)
You could also get this
Get this interactive HTML data dictionary in minutes with Dataedo.