Find tables without relationships - Loner Tables - in Vertica database

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