Find tables without foreign keys in Snowflake

Query below lists all tables that do not have foreign keys.

See also:

Query

select tab.table_schema,
       tab.table_name,
       '>- No FKs' as foreign_keys
from information_schema.tables tab
left join (select *
           from information_schema.table_constraints
           where constraint_type = 'FOREIGN KEY') fks
     on tab.table_name = fks.table_name
     and tab.table_schema = fks.table_schema
where fks.constraint_type is null
      and tab.table_type = 'BASE TABLE'
order by tab.table_schema,
         tab.table_name;

Columns

  • table_schema - name of the schema in which table resides
  • table_name - table name
  • foreign_keys - symbol indicating lack of FKs

Rows

  • One row represents one without foreign keys
  • Scope of rows: all tables without foreign keys (do not refer to other tables)
  • Ordered by schema name and table name

Sample results

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