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

Rene Castro - Dataedo Team Rene Castro 2018-12-10

Table of Contents:


    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.

    By continuing to use our website, you consent to the use of cookies in accordance with our Privacy Policy (updated 28-04-2020).
    Accept