The queries below show how many tables without primary keys there are in the user databases (schemas), and what percentage of the total tables represents.

Query

select count(*) as all_tables,
       count(*) - count(tco.constraint_type) as no_pk_tables,
       cast( 100.0*(count(*) - count(tco.constraint_type)) / count(*)
            as decimal(5,2)) as no_pk_percent
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 tab.table_type = 'BASE TABLE'
    -- and tab.table_schema = 'database_name' -- put your database name here
      and tab.table_schema not in('mysql', 'information_schema',
                                  'sys', 'performance_schema');

Columns

• all_tables - number of all tables in the database
• no_pk_tables - number of tables without a primary key
• no_pk_percent - percentage of tables without primary key in all tables

Rows

Query returns just one row.

Sample results