List foreign keys in Snowflake

This query lists foreign keys constraints with referenced constraints.

Query

select fk_tco.table_schema as foreign_schema,
       fk_tco.table_name as foreign_table,
       fk_tco.constraint_name as foreign_constraint,
       '>-' as rel,
       pk_tco.table_schema as referenced_schema,
       pk_tco.table_name as referenced_table,
       pk_tco.constraint_name as referenced_constraint
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
order by fk_tco.table_schema,
         fk_tco.table_name; 

Columns

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

Rows

  • One row represents one foreign key constraint
  • Scope of rows: all foregin keys in a database
  • Ordered by schema name and name of foreign table

Sample results

You could also get this

Get this interactive HTML data dictionary in minutes with Dataedo.

See live HTML data dictionary sample

Try for free