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.

Query

select tco.table_schema,
       tco.table_name,
       tco.constraint_type,
       tco.constraint_name,
       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,
       table_name,
       '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_name;

Columns

  • 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

Rows

  • 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.
0
There are no comments. Click here to write the first comment.