page305
393
This SQL statement works for the previous
SHOW
commands, and note the explicit
ordering of
path_to_obejct
and
key_sequence
to ensure primary key attributes are
displayed consecutively.
SELECT "database_name"||'.'||"schema_name"||'.'||"table_name" AS path_
to_object,
"column_name",
"key_sequence",
"constraint_name"
FROM TABLE ( RESULT_SCAN ( last_query_id()))
ORDER BY path_to_object, "key_sequence";
You might also use the
referential_constraints
view though the results are less
useful. I leave this to you for your further investigation. See
https://docs.snowflake.
com/en/sql-reference/info-schema/referential_constraints
. A fuller explanation
of how constraints are supported can be found at
https://docs.snowflake.com/en/
sql-reference/constraints-overview
.
GET_DDL
As you have seen, Snowflake does not provide a simple method to identify objects
related by referential integrity; you can only access related information, not the
relationship information.
An alternative method for identifying referential integrity declarations is to examine
each object definition. In this example, you use the
get_ddl
function to extract object
metadata for the table
client_mask
.
SELECT get_ddl ( 'TABLE', 'TPC.TPC_OWNER.CLIENT_MASK', TRUE );
The returned string contains the following:
create or replace TABLE CLIENT_MASK (
CLIENT_MASK_ID NUMBER(38,0) NOT NULL,
CLIENT_ID NUMBER(38,0),
MASK_NAME VARCHAR(255),
MASK_ATTRIBUTE VARCHAR(255),
MASK_VALUE VARCHAR(255),
ChapTer 10
OpTimizing perfOrmanCe