How many tables don't have primary keys (with percentage)

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

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 as tab
        left outer join sys.indexes as 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

Columns

  • 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

Rows

The query returns just one row.

Sample results

Sample results that show how many tables don't have primary keys in the AdventureWorksLT database and the percentage it represents.

0
There are no comments. Click here to write the first comment.