List tables with most foreign keys in Redshift

Query below lists tables with their number of foreign keys and number of tables they refer to.

See also:

Query

select fk_tco.table_schema || '.' || fk_tco.table_name as table_name,
       count(*) as foreign_keys,
       count(distinct pk_tco.table_name) as referenced_tables
from information_schema.referential_constraints rco
join information_schema.table_constraints fk_tco 
     on rco.constraint_name = fk_tco.constraint_name
     and rco.constraint_schema = fk_tco.table_schema
join information_schema.table_constraints pk_tco
     on rco.unique_constraint_name = pk_tco.constraint_name
     and rco.unique_constraint_schema = pk_tco.table_schema
group by 1
order by count(*) desc;

Columns

  • table_name - table with schema name
  • foreign_keys - number of foreign keys in a table
  • referenced_tables - number of referenced tables. Note that it is not the same as number of foreign keys, as multiple foreign keys may reference the same table.

Rows

  • One row represents one table in a database
  • Scope of rows: tables in a database that have foreign keys (reference other tables)
  • Ordered by number of foreign keys from the ones with the most

Sample results

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