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.
See live HTML data dictionary sample