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

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(100.0 * no_pk.tables / all_tabs.tables as decimal (14,2)) concat '%'
    as no_pk_percent
from
    (select count(*) as tables
    from 
    syscat.tables tab
    left outer join syscat.tabconst const 
        on const.tabschema = tab.tabschema 
        and const.tabname = tab.tabname and const.type ='P'
    where 
        tab.type ='T'
        and tab.tabschema not like 'SYS%'
        and const.constname is null
    ) as no_pk
inner join 
    (select count(*) as tables
    from syscat.tables 
    where type = 'T') 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

Query returns just one row.

Sample results

I checked how many tables in GSDB database do not have primary keys. It turns out it is only 4.62% which for me is a good result.

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