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

Query

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;

Columns

  • 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

Rows

  • Scope of rows: query returns one row

Sample results

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