Query below lists tables and their primary key (PK) constraint names. By browsing list you can spot which tables have and which don't have primary keys.

See also: tables without primary keys.

Query

select tab.table_schema,
       tab.table_name,
       tco.constraint_name,
       tco.constraint_type
from information_schema.tables tab
left join information_schema.table_constraints tco
          on tab.table_schema = tco.table_schema
          and tab.table_name = tco.table_name
          and tco.constraint_type = 'PRIMARY KEY'
where tab.table_type = 'BASE TABLE'
order by table_schema,
         table_name;

Primary Key Columns

Snowflake doesn't allow us to simple list PKs columns by one query. To see tables PK you can use following command

describe table table_name;

Columns

• table_schema -Schema that the table belongs to
• table_name - Name of the table
• constraint_name - Name of the PK's constraint or null if table hasn't PK.
• constraint_type - Type of the constraint or null if table hasn't PK.

Rows

• One row represents one table in a database
• Scope of rows: all tables in a database
• Ordered by schema name, table name

Sample results

You can see what are the names of PK constraints for each table and which tables don't have PKs at all

Result of desc command

You could also get this

Get this interactive HTML data dictionary in minutes with Dataedo.

See live HTML data dictionary sample

Try for free