This query listed tables without primary keys and this one shows how many of them there are and what is the percentage of total tables.

Query

with tables AS (
    select distinct tab.table_schema,
            tab.table_name,
           case when pk.constraint_id is null
                then 1
                else 0 end as no_pk
    from v_catalog.tables tab
    left join v_catalog.primary_keys pk
              on tab.table_schema = pk.table_schema
              and tab.table_name = pk.table_name
    order by tab.table_schema,
             tab.table_name
)
select count(*) as tables,
       sum(no_pk) as no_pk_tables,
       100.00*sum(no_pk)/count(*) as no_pk_percent
from tables;

Columns

• tables - number of all tables in a 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