Find tables without relationships - Loner Tables - in Vertica database

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

Table of Contents:


    Query below lists tables that are not referencing and are not referenced by other tables. We call this type of tables a Loner Tables. This diagram illustrates the concept:

    Learn more about Loner Tables

    See also:

    Query

    select 'no FKs >-' as refs,
           tab.table_schema || '.' || tab.table_name as table,
           '>- no FKs' as foreign_keys
    from v_catalog.tables tab
    left join v_catalog.foreign_keys fks
              on tab.table_schema = fks.table_schema
              and tab.table_name = fks.table_name
    left join v_catalog.foreign_keys ref
              on tab.table_schema = ref.reference_table_schema
              and tab.table_name = ref.reference_table_name
    where (fks.constraint_id is null and ref.constraint_id is null)
    order by tab.table_schema,
             tab.table_name;
    

    Columns

    • refs - icon symbolizing lack of references by foregin key
    • table - schema name followed by table name
    • foreign_keys - icon symbolizing lack of foregin key

    Rows

    • One row represents one table
    • Scope of rows: tables that are not related with any table
    • Ordered by schema name and table name

    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