Query below returns all primary keys and unique key constraints on tables in Snowflake database.

Query

select table_schema,
       table_name,
       constraint_name,
       constraint_type 
from information_schema.table_constraints
where constraint_type in ('PRIMARY KEY', 'UNIQUE')
order by table_schema, table_name;

Columns

• table_schema - schema name of table
• table_name - name of table
• constraint_name - name of the constraint
• constraint_type
  • PRIMARY KEY - for primary keys
  • UNIQUE - for constraints created with CONSTRAINT UNIQUE statement

Rows

• One row represents one constraint in the database.
• Scope of rows: all PKs, UKs
• Ordered by schema name and table name

Sample results