如何查询Snowflake指定Schema下的表父子关联关系？

核心查询逻辑 #

Snowflake中显式定义的外键约束（即表之间的父子关联关系）存储在INFORMATION_SCHEMA下的系统视图中，你可以通过关联TABLE_CONSTRAINTS、REFERENTIAL_CONSTRAINTS、KEY_COLUMN_USAGE三个视图获取完整的关联信息。

可直接使用的查询语句 #

SELECT
    -- 子表（外键所属表）信息
    tc.table_schema AS child_schema,
    tc.table_name AS child_table,
    kcu.column_name AS child_foreign_key_column,
    -- 父表（被引用的主键所属表）信息
    ccu.table_schema AS parent_schema,
    ccu.table_name AS parent_table,
    ccu.column_name AS parent_primary_key_column,
    tc.constraint_name AS foreign_key_constraint_name
FROM
    INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
    ON tc.constraint_catalog = kcu.constraint_catalog
    AND tc.constraint_schema = kcu.constraint_schema
    AND tc.constraint_name = kcu.constraint_name
INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc
    ON tc.constraint_catalog = rc.constraint_catalog
    AND tc.constraint_schema = rc.constraint_schema
    AND tc.constraint_name = rc.constraint_name
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE ccu
    ON rc.unique_constraint_catalog = ccu.constraint_catalog
    AND rc.unique_constraint_schema = ccu.constraint_schema
    AND rc.unique_constraint_name = ccu.constraint_name
    AND kcu.ordinal_position = ccu.ordinal_position
WHERE
    tc.constraint_type = 'FOREIGN KEY'
    -- 替换为你的目标数据库名称
    AND tc.table_catalog = 'YOUR_DATABASE'
    -- 替换为你的目标Schema名称，不需要过滤Schema可删除该行
    AND tc.table_schema = 'YOUR_TARGET_SCHEMA'
ORDER BY child_schema, child_table;

注意事项 #

• 只有建表时显式定义了外键约束的关联关系才可以通过上述语句查询到。Snowflake默认不强制外键约束校验，但只要建表时声明了外键，就会存储到系统视图中。
• 如果你的表未显式声明外键约束，系统视图不会存储对应关联关系，需要你通过ETL代码逻辑、SQL查询历史、数据血缘功能手动推导关联。
• 联合主键对应的多字段外键关联也会在查询结果中按ordinal_position顺序返回对应字段映射。

内容的提问来源于stack exchange，提问作者jmizzo