page304
392
FROM snowflake.account_usage.object_dependencies
WHERE referencing_database = 'TPC'
AND referencing_schema = 'TPC_OWNER'
START WITH referencing_object_name = 'V_CLIENT_SQL_STATEMENT'
CONNECT BY referencing_object_name = PRIOR referenced_object_name
ORDER BY 1;
These approaches also work for materialized views but do not work for
dynamic tables.
You can find more information on the object_dependencies view at
https://docs.
snowflake.com/en/sql-reference/account-usage/object_dependencies
. You can
find a fuller explanation of how object dependencies are tracked at
https://docs.
snowflake.com/en/user-guide/object-dependencies
.
Identifying Constraints
Referential integrity may exist across more than one database in an account. Snowflake
maintains referential integrity metadata in the Account Usage Store view
table_
constraints
. Note that latency of up to two hours may be experienced.
SELECT table_catalog||'.'||
table_schema||'.'||
table_name AS path_to_object,
constraint_name,
constraint_type
FROM snowflake.account_usage.table_constraints
WHERE constraint_catalog = 'TPC'
AND constraint_schema = 'TPC_OWNER'
AND deleted IS NULL
ORDER BY 1;
You can find more information on the
table_constraints
view at
https://docs.
snowflake.com/en/sql-reference/account-usage/table_constraints
.
For primary keys, only the
SHOW
command proves useful. Note the addition of
attributes that comprise the primary key.
SHOW PRIMARY KEYS IN SCHEMA;
ChapTer 10
OpTimizing perfOrmanCe