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

Bart Gawrych - Dataedo Team Bart Gawrych 2019-10-31

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

    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

    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