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

Query

Generic query for the specified database (schema)

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 (schema)
• no_pk_tables - number of tables without a primary key
• no_pk_percent - percentage of tables without primary key

Rows

The query returns just one row.

Sample results