The query below lists the tables and their primary key (PK) constraint names in all user databases. By browsing the list, you can spot which tables have and which don't have primary keys.
See also: tables without primary keys.
Query
select tab.table_schema as database_name,
tab.table_name,
tco.constraint_name as pk_name,
group_concat(kcu.column_name
order by kcu.ordinal_position
separator ', ') as columns
from information_schema.tables tab
left join information_schema.table_constraints tco
on tab.table_schema = tco.table_schema
and tab.table_name = tco.table_name
and tco.constraint_type = 'PRIMARY KEY'
left 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 tab.table_schema not in ('mysql', 'information_schema')
group by tab.table_schema,
tab.table_name,
tco.constraint_name
order by tab.table_schema,
tab.table_name;
Columns
- database_name - name of the database (schema)
- table_name - name of the table
- pk_name - primary key constraint name
- columns - list of PK columns separated by ','
Rows
- One row: represents one table in a database (schema)
- Scope of rows: all tables in the databases (schemas)
- Ordered by: database (schema) name, table name
Sample results
You could also get this
Get this interactive HTML data dictionary in minutes with Dataedo.