The query below lists tables in the user databases (schemas) without primary keys.

Query

select tab.table_schema as database_name,
       tab.table_name
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'
where tco.constraint_type is null
      and tab.table_schema not in('mysql', 'information_schema', 
                                  'performance_schema', 'sys')
      and tab.table_type = 'BASE TABLE'
--      and tab.table_schema = 'sakila' -- put schema name here
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 - database (schema) name
• table_name - table name

Rows

• One row: represents one table without primary key in a database (schema)
• Scope of rows: all tables without primary keys in a database (schema)
• Ordered by: database (schema) name, table name

Sample results

Sample results for all databases (schemas)