List all tables refrenced by specific table (by FK) in Vertica database

Query below lists all tables refrenced with foregin key by specific table.

Query

select table_schema || '.' || table_name  as fk_table,
       '>-' as rel,
       reference_table_schema || '.' || reference_table_name
            as primary_table
from v_catalog.foreign_keys 
where table_name = 'store_orders_fact'
      -- and table_schema = 'store'
order by primary_table;

Columns

  • fk_table - foreign table name with schema name - the table you provided as a parameter
  • rel - relationship symbol implicating FK and direction
  • primary_table_name - primary (rerefenced) tables names with schema name - the tables you are looking for

Rows

  • One row represents one referenced table
  • Scope of rows: all tables referenced by table with provided name (and optionally schema)
  • Ordered by referenced table schema and name

Notes

  • There can be more tables with the same name. If that's the case, uncomment where clause and provide schema name

Sample results

All tables referenced with FK by store.store_orders_fact table in VMart database.

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