How many tables don't have primary keys (with percentage) in Teradata 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

    SELECT  COUNT(*) AS Tables,
           COUNT(*) - COUNT(i.TableName) AS NoPKTables,
           100.0*(COUNT(*) - COUNT(i.TableName)) / COUNT(*) AS NoPKPercent
    FROM    DBC.TablesV t
    LEFT JOIN   DBC.IndicesV i
    ON  t.DataBaseName = i.DataBaseName
    AND t.TableName = i.TableName
    AND i.IndexType = 'K'
    WHERE t.TableKind = 'T'
    AND t.DataBaseName NOT IN ('All', 'Crashdumps', 'DBC', 'dbcmngr', 
        'Default', 'External_AP', 'EXTUSER', 'LockLogShredder', 'PUBLIC',
        'Sys_Calendar', 'SysAdmin', 'SYSBAR', 'SYSJDBC', 'SYSLIB', 
        'SystemFe', 'SYSUDTLIB', 'SYSUIF', 'TD_SERVER_DB',  'TDStats',
        'TD_SYSGPL', 'TD_SYSXML', 'TDMaps', 'TDPUSER', 'TDQCD',
        'tdwm', 'SQLJ', 'TD_SYSFNLIB', 'SYSSPATIAL');
    

    Columns

    • Tables - number of all tables in a database
    • NoPKTables - number of tables without a primary key
    • NoPKPercent - 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