List all table constraints (PK, FK, Unique, Default) in Snowflake

Query below lists all table (and view) constraints - primary keys, unique constraints , foreign keys and default constraints.


select tco.table_schema,
       rel_tco.table_schema || '.' || rel_tco.table_name as constraint_details
from information_schema.table_constraints tco
left join information_schema.referential_constraints rco
          on tco.constraint_schema = rco.constraint_schema
          and tco.constraint_name = rco.constraint_name
left join information_schema.table_constraints rel_tco
          on rco.unique_constraint_schema = rel_tco.constraint_schema
          and rco.unique_constraint_name = rel_tco.constraint_name
where (tco.constraint_type = 'FOREIGN KEY'
       and rel_tco.constraint_name is not null) 
      or tco.constraint_type != 'FOREIGN KEY'
union all
select table_schema,
       'DEFAULT CONSTRAINT' as constraint_type,
       'CHECK ' || column_name as constraint_name,
       column_default as details
from information_schema.columns
where column_default is not null
order by table_schema,


  • table_schema - name of the table schema
  • table_name - name of the table
  • constraint_type - type of constraint:
    • Primary key
    • Foregin key
    • Unique
    • Default constraint
  • constraint_name - name of constraint or index
  • constraint_details - details of this constraint:
    • Primary key - NULL
    • Unique - NULL
    • Foregin key - parent table name
    • Default constraint - column name and default value definition


  • One row represents one constraint: PK,FK, Unique, Default
  • Scope of rows: all constraints
  • Ordered by schema 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.