Find tables without foreign keys in Snowflake

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

See also:


select tab.table_schema,
       '>- 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,


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


  • 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

Comments are only visible when the visitor has consented to statistics cookies. To see and add comments please accept statistics cookies.
There are no comments. Click here to write the first comment.