如何查询Snowflake中指定存储过程/函数涉及的表、视图与列

你之前使用的SHOW命令仅能返回对象基础元数据与定义文本，无法直接解析内部关联的依赖对象，可通过Snowflake自带的系统视图实现需求：

1. 查询依赖的表/视图（全版本支持） #

使用ACCOUNT_USAGE下的OBJECT_DEPENDENCIES视图可直接查询静态定义的依赖对象，无需手动解析代码：

SELECT 
  REFERENCED_DATABASE_NAME AS 依赖库名,
  REFERENCED_SCHEMA_NAME AS 依赖Schema名,
  REFERENCED_OBJECT_NAME AS 依赖对象名,
  REFERENCED_OBJECT_TYPE AS 依赖对象类型
FROM SNOWFLAKE.ACCOUNT_USAGE.OBJECT_DEPENDENCIES
WHERE 
  DATABASE_NAME = UPPER('{db_name}')
  AND SCHEMA_NAME = UPPER('{schema_name}')
  AND OBJECT_NAME = UPPER('{object_name}')
  AND OBJECT_TYPE IN ('FUNCTION', 'PROCEDURE')
  AND REFERENCED_OBJECT_TYPE IN ('TABLE', 'VIEW');

注意：

1. 如果你的存储过程/函数是用带引号的大小写混合名称创建的，需要去掉UPPER()函数，严格匹配名称大小写
2. 该视图仅能解析静态定义的依赖，如果存储过程/函数内用到动态SQL，动态生成的SQL涉及的表视图不会被统计，需要搭配下文的列级查询方案使用

2. 查询关联列字段（企业版及以上支持） #

如果需要列级依赖信息，可使用ACCESS_HISTORY视图基于历史执行记录查询：

SELECT DISTINCT
  O.value:"objectName"::STRING AS 表/视图名,
  C.value:"columnName"::STRING AS 关联列名
FROM SNOWFLAKE.ACCOUNT_USAGE.ACCESS_HISTORY AH,
LATERAL FLATTEN(input => AH.BASE_OBJECTS_ACCESSED) O,
LATERAL FLATTEN(input => O.value:"columns") C
WHERE 
  AH.QUERY_ID IN (
    SELECT QUERY_ID 
    FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
    WHERE 
      QUERY_TYPE IN ('CALL', 'SELECT')
      AND DATABASE_NAME = UPPER('{db_name}')
      AND SCHEMA_NAME = UPPER('{schema_name}')
      AND (QUERY_TEXT ILIKE '%CALL '||UPPER('{object_name}')||'%' 
        OR QUERY_TEXT ILIKE '%'||UPPER('{object_name}')||'%')
  )
  AND O.value:"objectDomain"::STRING IN ('Table', 'View');

补充说明：

1. ACCESS_HISTORY视图有最多90分钟的数据延迟，默认保留1年的访问记录
2. 仅能统计到过去1年内有执行记录的存储过程/函数的列依赖，无执行记录的对象不会返回数据
3. 如果无企业版权限，只能手动解析存储过程/函数的DEFINITION字段匹配列名，准确率会受代码写法影响，动态SQL场景无法解析

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