List all tables referencing a specific table (by FK) in Snowflake

Query below returns tables that are refrencing provided table with foregin keys.

Query

select fk_tco.table_schema as foreign_schema,
       fk_tco.table_name as foreign_table,
       '>-' as rel,
       pk_tco.table_schema as referenced_schema,
       pk_tco.table_name as referenced_table
from information_schema.referential_constraints rco
join information_schema.table_constraints fk_tco 
      on fk_tco.constraint_name = rco.constraint_name
      and fk_tco.constraint_schema = rco.constraint_schema
join information_schema.table_constraints pk_tco
      on pk_tco.constraint_name = rco.unique_constraint_name
      and pk_tco.constraint_schema = rco.unique_constraint_schema
where pk_tco.table_name = 'TABLE_NAME' -- enter table name here
      -- and fk_tco.table_schema = 'schema name'
order by fk_tco.table_schema,
         fk_tco.table_name;

Columns

  • foreign_schema- foreign schema name
  • foreign_table - foreign table name
  • rel - relationship symbol implicating direction
  • referenced_schema - rerefenced schema name
  • referenced_table - rerefenced table name

Rows

  • One row represents one table which refers to provided table
  • Scope of rows: all tables referencing specific table
  • Ordered by foreign table schema and name

Sample results

Referencing tables to ADDRESS table in Snowflake.

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