Loner Ratio - how many tables have no relationships in Teradata database

Query below count something we call Loner Tables and return it's statistics in database. This diagram illustrates the concept of loner tables:

Query

SELECT COUNT(*) AS Tables,
       SUM(CASE WHEN Rel.TableName IS NULL
                THEN 1 ELSE 0 END) AS LonerTables,
       100.00*SUM(CASE WHEN Rel.TableName IS NULL
                       THEN 1 ELSE 0 END)
            /COUNT(*) AS LonerRatio
FROM    DBC.TablesV Tab
LEFT JOIN   (
            SELECT DISTINCT ParentDb AS DatabaseName,
                   ParentTable AS TableName
            FROM All_RI_ParentsV
            UNION
            SELECT DISTINCT ChildDb,
                   ChildTable
            FROM All_RI_ChildrenV
) Rel   ON Rel.DatabaseName = Tab.DatabaseName
        AND Rel.TableName = Tab.TableName
WHERE   Tab.TableKind = 'T'
AND Tab.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 tables in database
  • LonerTables - number of Loner Tables in the database
  • LonerRatio - Loner Ratio - % of Loner Tables in the database

Rows

  • Scope of rows: query returns one row

Sample results

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