WWDVC-2024-rev-stacked.png
Snowflake:Getting Foreign Key Columns
Normalization Data Models

Snowflake DB is a phenomenal database, yet getting foreign key columns is difficult without using SHOW commands.  These SHOW commands only execute with specific privileges.  The case is: the missing INFORMATION_SCHEMA . KEY_COLUMN_USAGE view / table.   In this technical blog entry I will provide you with a Snowflake DB procedure that will populate a KEY_COLUMN_USAGE table so you can use Select statements to pull data out.  The procedure I provide runs every time you call it.  BUT: it does not check to see if the row/record is already loaded.  Feel free to embellish the code as you see fit.

The code must be a procedure in order to execute the SHOW commands.   SHOW commands give an erroneous error when executed as a function.  It is up to you to run the proc, refresh the metadata when you want to.  It is up to you to delete or remove metadata from the KEY_COLUMN_USAGE table we populate.

I hope this helps you, if you like what you see – feel free to drop us a note on our Contact Us form.

Other modifications you might want to make?

  • adding TRY/CATCH to catch errors that might happen
  • adding a return code of ZERO for errors that might have happened
  • adding code to check/see if the row needs UPDATING or delete/insert (instead of update in place)
  • adding the create table code inside the proc, in case the table doesn’t exist when the proc is run
  • adding a LIKE clause to the table retrieval (switching SHOW TABLES over to a SELECT from information_schema.tables command)

You will need a SCHEMA:  META_CONFIG

The KEY_COLUMN_USAGE TABLE:

drop table if exists META_CONFIG.KEY_COLUMN_USAGE CASCADE;

create table META_CONFIG.KEY_COLUMN_USAGE
(
constraint_catalog varchar(128) NOT NULL,
constraint_schema varchar(128) NOT NULL,
constraint_name varchar(128) NOT NULL,
table_catalog varchar(128) NOT NULL,
table_schema varchar(128) NOT NULL,
table_name varchar(128) NOT NULL,
column_name varchar(128) NOT NULL,
ordinal_position int NOT NULL default 1,
position_in_unique_constraint int NULL
);

Stored Procedure Code:

create or replace procedure META_CONFIG.POPULATE_KEY_COLUMN_USAGE(PSCHEMA varchar, PTABLE VARCHAR)
    returns varchar
    language javascript
    EXECUTE AS CALLER
    as
    $$
        // grab all tables in this schema
        var stmt = snowflake.createStatement(
            {sqlText: 'SHOW TABLES IN SCHEMA '+PSCHEMA}
        );
        var tblsResult = stmt.execute();
        var pkresult = '';
        var fkresult = '';
        var tblName  = '';

        while (tblsResult.next()) {
            if (tblsResult.getColumnValue('is_external') === 'Y') {
                continue;
            }

            // if table name specified then only get keys for that table
            tblName = tblsResult.getColumnValue('name');
            if (PTABLE !== '' && tblName !== PTABLE) {
                continue;
            }

            stmt = snowflake.createStatement(
                {sqlText: "SHOW PRIMARY KEYS IN "+PSCHEMA + "."+tblName}
                );
            pkresult = stmt.execute();

            while (pkresult.next()) {
              stmt = snowflake.createStatement(
                  {sqlText:'INSERT INTO META_CONFIG.KEY_COLUMN_USAGE ('+
                  'constraint_catalog, '+
                  'constraint_schema, '+
                  'constraint_name, '+
                  'table_catalog, ' +
                  'table_schema, '+
                  'table_name, '+
                  'column_name, '+
                  'ordinal_position, '+
                  'position_in_unique_constraint '+
				  ') '+
                  'VALUES (:1,:2,:3,:4,:5,:6,:7,:8,:9)',
                binds: [
                  pkresult.getColumnValue('database_name'),
                  pkresult.getColumnValue('schema_name'),
                  pkresult.getColumnValue('constraint_name'),
     			  pkresult.getColumnValue('database_name'),
                  pkresult.getColumnValue('schema_name'),
                  pkresult.getColumnValue('table_name'),
                  pkresult.getColumnValue('column_name'),
                  pkresult.getColumnValue('key_sequence'),
                  pkresult.getColumnValue('key_sequence')
                  ]
                  } );
              stmt.execute();
            }

            stmt = snowflake.createStatement(
                {sqlText: 'SHOW IMPORTED KEYS IN '+PSCHEMA+'.'+tblName}
                );
            fkresult = stmt.execute();

            while (fkresult.next()) {
              stmt = snowflake.createStatement(
                  {sqlText:'INSERT INTO META_CONFIG.KEY_COLUMN_USAGE ('+
                  'constraint_catalog, '+
                  'constraint_schema, '+
                  'constraint_name, '+
                  'table_catalog, ' +
                  'table_schema, '+
                  'table_name, '+
                  'column_name, '+
                  'ordinal_position, '+
                  'position_in_unique_constraint '+
				  ') '+
                  'VALUES (:1,:2,:3,:4,:5,:6,:7,:8,:9)',
                binds: [
                  pkresult.getColumnValue('fk_database_name'),
                  pkresult.getColumnValue('fk_schema_name'),
                  pkresult.getColumnValue('fk_name'),
     			  pkresult.getColumnValue('fk_database_name'),
                  pkresult.getColumnValue('fk_schema_name'),
                  pkresult.getColumnValue('fk_table_name'),
                  pkresult.getColumnValue('fk_column_name'),
                  pkresult.getColumnValue('key_sequence'),
				  '0'
                  ]
                  } );
              stmt.execute();
            }
        }

        return 1;
  $$
 ;

Executing the Procedure:

 call META_CONFIG.POPULATE_KEY_COLUMN_USAGE('MYSCHEMA','MYTABLE');

Where:

  • MY SCHEMA can be replaced with the schema you wish to scan / collect metadata on keys for.  If MY SCHEMA is left blank, then the procedure will simply exit.
  • MY TABLE can be left blank to load ALL tables in that schema, if MY TABLE is populated it must match verbatim the name of the table you want scanned.

Views that USE the meta-data

drop view if exists meta_config.v_infoschema_fk_columns cascade;

create or replace view meta_config.v_infoschema_fk_columns as
  SELECT
     KCU1.CONSTRAINT_SCHEMA AS FK_CONSTRAINT_SCHEMA
    ,KCU1.CONSTRAINT_NAME AS FK_CONSTRAINT_NAME
    ,KCU1.TABLE_SCHEMA AS FK_TABLE_SCHEMA
    ,KCU1.TABLE_NAME AS FK_TABLE_NAME
    ,KCU1.COLUMN_NAME AS FK_COLUMN_NAME
    ,KCU1.ORDINAL_POSITION AS FK_ORDINAL_POSITION
    ,KCU2.CONSTRAINT_SCHEMA AS REFERENCED_CONSTRAINT_SCHEMA
    ,KCU2.CONSTRAINT_NAME AS REFERENCED_CONSTRAINT_NAME
    ,KCU2.TABLE_SCHEMA AS REFERENCED_TABLE_SCHEMA
    ,KCU2.TABLE_NAME AS REFERENCED_TABLE_NAME
    ,KCU2.COLUMN_NAME AS REFERENCED_COLUMN_NAME
    ,KCU2.ORDINAL_POSITION AS REFERENCED_ORDINAL_POSITION
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS RC
INNER JOIN META_CONFIG.KEY_COLUMN_USAGE AS KCU1
    ON KCU1.CONSTRAINT_CATALOG = RC.CONSTRAINT_CATALOG
    AND KCU1.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA
    AND KCU1.CONSTRAINT_NAME = RC.CONSTRAINT_NAME
INNER JOIN META_CONFIG.KEY_COLUMN_USAGE AS KCU2
    ON KCU2.CONSTRAINT_CATALOG = RC.UNIQUE_CONSTRAINT_CATALOG
    AND KCU2.CONSTRAINT_SCHEMA = RC.UNIQUE_CONSTRAINT_SCHEMA
    AND KCU2.CONSTRAINT_NAME = RC.UNIQUE_CONSTRAINT_NAME
    AND KCU2.ORDINAL_POSITION = KCU1.ORDINAL_POSITION ;


drop view if exists meta_config.v_infoschema_fk_tables cascade;

create or replace view meta_config.v_infoschema_fk_tables as
 SELECT distinct
     KCU1.CONSTRAINT_SCHEMA AS FK_CONSTRAINT_SCHEMA
    ,KCU1.CONSTRAINT_NAME AS FK_CONSTRAINT_NAME
    ,KCU1.TABLE_SCHEMA AS FK_TABLE_SCHEMA
    ,KCU1.TABLE_NAME AS FK_TABLE_NAME
    ,KCU2.CONSTRAINT_SCHEMA AS REFERENCED_CONSTRAINT_SCHEMA
    ,KCU2.CONSTRAINT_NAME AS REFERENCED_CONSTRAINT_NAME
    ,KCU2.TABLE_SCHEMA AS REFERENCED_TABLE_SCHEMA
    ,KCU2.TABLE_NAME AS REFERENCED_TABLE_NAME
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS RC
INNER JOIN META_CONFIG.KEY_COLUMN_USAGE AS KCU1
    ON KCU1.CONSTRAINT_CATALOG = RC.CONSTRAINT_CATALOG
    AND KCU1.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA
    AND KCU1.CONSTRAINT_NAME = RC.CONSTRAINT_NAME
INNER JOIN META_CONFIG.KEY_COLUMN_USAGE AS KCU2
    ON KCU2.CONSTRAINT_CATALOG = RC.UNIQUE_CONSTRAINT_CATALOG
    AND KCU2.CONSTRAINT_SCHEMA = RC.UNIQUE_CONSTRAINT_SCHEMA
    AND KCU2.CONSTRAINT_NAME = RC.UNIQUE_CONSTRAINT_NAME
    AND KCU2.ORDINAL_POSITION = KCU1.ORDINAL_POSITION ;

We hope you enjoy this code!  Let us know!
Cheers, Dan Linstedt

General Admission Pricing

MAIN PROGRAM


Monday-Friday
& 10th Anniversary Reception Tuesday evening

$997

Become a WWDVC Speaker

Submit your sessions and if selected, receive FREE registration!
Exhibitor Packages

All exhibitors will have a booth on the
exhibitor floor that you are responsible
for staffing. Includes a set number of staff tickets. Diamond Level: Includes a Hands-On Lab

Sponsor Package

Sponsors will have access
to attendee network but NO Booth. Sponsored breakfasts and lunches available. Will be featured on all group marketing material.

Scroll to Top