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.
select all_tabs.[tables] as all_tables, no_pk.[tables] as no_pk_tables, cast(cast(100.0 * no_pk.[tables] / all_tabs.[tables] as decimal(36, 1)) as varchar) + '%' as no_pk_percent from (select count(*) as [tables] from sys.tables tab left outer join sys.indexes pk on tab.object_id = pk.object_id and pk.is_primary_key = 1 where pk.object_id is null) as no_pk inner join (select count(*) as [tables] from sys.tables) as all_tabs on 1 = 1
- all_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
Query returns just one row.
I checked how many tables in Microsoft System Center Configuration Manager database do not have primary keys. It turns out it is only 1.3% which for me is a good result.