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

Query below return number of tables which are not referencing and are not referenced by other tables (Loner Tables).

Learn more about Loner Tables


with loners as (
    select distinct tab.table_schema || '.' || tab.table_name as table,
           case when (fks.constraint_id is null and ref.constraint_id is null)
            then 1
            else 0 end as is_loner
    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
select count(*) as tables,
       sum(is_loner) as loner_tables,
       100*round(sum(is_loner)/count(*),2) as loner_ratio
from loners;


  • tables - number of all tables in database
  • loner_tables - number of not related tables in the database
  • loner_ratio - Loner Ratio - % of Loner Tables in the database


  • Scope of rows: query returns one row

Sample results

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