List tables with most foreign keys in Vertica database

Query below allow to get number of references from tables.

See also:

Do you need a fortune teller to tell you about the data you have?

If you visited a fortune teller at least once in the past 12 months we highly recommend reading this article. Learn how to see into your data yourself.

Learn how

Query

select table_schema || '.' || table_name
            as table,
       count(*) as foreign_keys,
       count(distinct reference_table_schema ||'.'|| reference_table_name)
            as referenced_tables
from v_catalog.foreign_keys
group by table_schema,
         table_name
order by count(*) desc;

Columns

  • table_name - schema name followed with table name
  • foreign_keys - number of FKs in a table
  • referenced_tables - count of referenced tables. (Many foreign keys can reference to the same table)

Rows

  • One row represents one table
  • Scope of rows: all tables which are referencing to tables
  • Ordered by count of foreign keys descending

Sample results

Create beautiful and useful documentation of your Vertica

Generate convenient documentation of your databases in minutes and share it with your team. Capture and preserve tribal knowledge in shared repository.

See how it works