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', 
                                'performance_schema', 'sys')
--  and tab.table_schema = 'schema_name' -- provide schema name here
group by tab.table_schema,
         tab.table_name,
         tco.constraint_name
order by tab.table_schema,
         tab.table_name;

Note: if you need the information for a specific database (schema), then uncomment the table_schema line and provide your database 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.

See live HTML data dictionary sample

Try for free