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

Ania - Dataedo Team Ania 2019-02-04

Table of Contents:


    Query below lists:

    (A) % of tables accessible to the current user in Oracle database that don't have primary keys

    (B) % of tables in Oracle database that don't have primary keys

    Query was executed under the Oracle9i Database version.

    Query

    A. Tables accessible to the current user

    select count(*) as table_count,      
           sum(case when pks.table_name is null 
                        then 1
                    else 0
               end) as no_pk_tables,
           cast(100 * sum(case when pks.table_name is null 
                        then 1
                    else 0
               end) / count(*) as decimal(3, 1))
           as no_pk_percent
    from sys.all_tables t 
    left join (select distinct owner,
                               table_name
               from sys.all_constraints 
               where constraint_type = 'P'
               ) pks
               on t.owner = pks.owner
                  and t.table_name = pks.table_name
    -- excluding some Oracle maintained schemas
    where t.owner not in ('ANONYMOUS','CTXSYS','DBSNMP','EXFSYS', 'LBACSYS', 
       'MDSYS', 'MGMT_VIEW','OLAPSYS','OWBSYS','ORDPLUGINS', 'ORDSYS','OUTLN',
       'SI_INFORMTN_SCHEMA','SYS','SYSMAN','SYSTEM', 'TSMSYS','WK_TEST',
       'WKSYS', 'WKPROXY','WMSYS','XDB','APEX_040000', 'APEX_PUBLIC_USER','DIP',
       'FLOWS_30000','FLOWS_FILES','MDDATA', 'ORACLE_OCM', 'XS$NULL',
       'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR', 'PUBLIC');
    

    B. If you have privilege on dba_tables and dba_constraints

    select count(*) as table_count,      
           sum(case when pks.table_name is null 
                        then 1
                    else 0
               end) as no_pk_tables,
           cast(100 * sum(case when pks.table_name is null 
                        then 1
                    else 0
               end) / count(*) as decimal(3, 1))
           as no_pk_percent
    from sys.dba_tables t 
    left join (select distinct owner,
                               table_name
               from sys.dba_constraints 
               where constraint_type = 'P'
               ) pks
               on t.owner = pks.owner
                  and t.table_name = pks.table_name
    -- excluding some Oracle maintained schemas
    where t.owner not in ('ANONYMOUS','CTXSYS','DBSNMP','EXFSYS', 'LBACSYS', 
       'MDSYS', 'MGMT_VIEW','OLAPSYS','OWBSYS','ORDPLUGINS', 'ORDSYS','OUTLN',
       'SI_INFORMTN_SCHEMA','SYS','SYSMAN','SYSTEM', 'TSMSYS','WK_TEST',
       'WKSYS', 'WKPROXY','WMSYS','XDB','APEX_040000', 'APEX_PUBLIC_USER','DIP',
       'FLOWS_30000','FLOWS_FILES','MDDATA', 'ORACLE_OCM', 'XS$NULL',
       'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR', 'PUBLIC');
    

    Columns

    • table_count - number of tables in a database
    • no_pk_tables - number of tables without a primary key in a database
    • no_pk_percent - percentage of tables without primary key in a database

    Rows

    Query returns 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