[SOLVED] Unable to find column names in a FK constraint

Unable to find column names in a FK constraint

I have created two tables in Snowflake.

create or replace TRANSIENT TABLE TESTPARENT (
COL1 NUMBER(38,0) NOT NULL,
COL2 VARCHAR(16777216) NOT NULL,
COL3 VARCHAR(16777216) NOT NULL,
constraint UNIQ_COL3 unique (COL3)
);


create or replace TRANSIENT TABLE TESTCHILD3 (
COL_A NUMBER(38,0) NOT NULL,
COL_B NUMBER(38,0) NOT NULL,
ABCDEF VARCHAR(16777216) NOT NULL,
constraint FKEY_1 foreign key (COL_A, COL_B) references TEST_DB.PUBLIC.TESTPARENT1(COL1,COL2),
constraint FKEY_2 foreign key (ABCDEF) references TEST_DB.PUBLIC.TESTPARENT(COL3)
);

Now I want to execute a query and see the names of columns that are involved in FKEY_2 FOREIGN KEY in Table TESTCHILD3, but it seems like there are no DB Table/View that keeps this information. I can find out the column names for UNIQUE KEY & PRIMARY KEY but there is nothing for FOREIGN KEYS.

EDIT I have already tried INFORMATION_SCHEMA.TABLE_CONSTRAINTS, along with INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS and all the other system tables. No luck. Only DESC TABLE is giving me some info related to CONSTRAINTS and COLUMNS but that also has FOREIGN KEY CONSTRAINTS information missing.

Solution

-- Step 1: Run SHOW PRIMARY KEYS

SHOW PRIMARY KEYS IN TABLE my_schema.my_table;

-- Step 2: Query the result

SELECT *

FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));  
or  
show primary keys in schema "DEV"."DP"  ;

CREATE TABLE "DEV"."DP"."DP_CONSTRAINT_COLUMNS" AS

SELECT  *

FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));