请求：查询Snowflake中所有Task调用的存储过程/函数

方法1：通过INFORMATION_SCHEMA解析任务定义 #

Snowflake的INFORMATION_SCHEMA.TASKS视图包含任务的完整定义，可通过正则表达式提取其中的存储过程和函数调用：

SELECT
    t.task_name,
    t.schema_name,
    t.database_name,
    -- 匹配存储过程调用（含Schema或不含Schema的格式）
    REGEXP_SUBSTR(t.definition, 'CALL\\s+(\"?[A-Za-z0-9_]+\"?\\.)?\"?[A-Za-z0-9_]+\"?\\s*\\(', 1, 1, 'i') AS called_procedure,
    -- 匹配函数调用（排除CALL语句里的存储过程）
    REGEXP_SUBSTR(t.definition, '(?!CALL\\s+)(\"?[A-Za-z0-9_]+\"?\\.)?\"?[A-Za-z0-9_]+\"?\\s*\\(', 1, 1, 'i') AS called_function
FROM
    INFORMATION_SCHEMA.TASKS t
WHERE
    t.definition LIKE '%CALL%' OR t.definition LIKE '%(%'
ORDER BY
    t.database_name, t.schema_name, t.task_name;

提示：如果你的对象名包含特殊字符，需要调整正则表达式的匹配规则。

方法2：用JavaScript存储过程批量解析复杂任务定义 #

如果任务里有分支逻辑等复杂调用场景，可通过存储过程遍历并提取所有依赖：

CREATE OR REPLACE PROCEDURE EXTRACT_TASK_DEPENDENCIES()
RETURNS TABLE(task_name VARCHAR, schema_name VARCHAR, database_name VARCHAR, object_type VARCHAR, object_name VARCHAR)
LANGUAGE JAVASCRIPT
AS
$$
    const result = [];
    // 查询所有任务的定义
    const stmt = snowflake.createStatement({sqlText: "SELECT TASK_NAME, SCHEMA_NAME, DATABASE_NAME, DEFINITION FROM INFORMATION_SCHEMA.TASKS"});
    const rs = stmt.execute();
    
    while (rs.next()) {
        const taskName = rs.getColumnValue(1);
        const schemaName = rs.getColumnValue(2);
        const dbName = rs.getColumnValue(3);
        const definition = rs.getColumnValue(4).toUpperCase();
        
        // 提取存储过程调用
        const procRegex = /CALL\s+([A-Z0-9_".]+)\s*\(/g;
        let match;
        while ((match = procRegex.exec(definition)) !== null) {
            result.push({
                TASK_NAME: taskName,
                SCHEMA_NAME: schemaName,
                DATABASE_NAME: dbName,
                OBJECT_TYPE: 'PROCEDURE',
                OBJECT_NAME: match[1]
            });
        }
        
        // 提取函数调用，排除已匹配的存储过程
        const funcRegex = /([A-Z0-9_".]+)\s*\(/g;
        while ((match = funcRegex.exec(definition)) !== null) {
            if (!definition.includes(`CALL ${match[1]}`)) {
                result.push({
                    TASK_NAME: taskName,
                    SCHEMA_NAME: schemaName,
                    DATABASE_NAME: dbName,
                    OBJECT_TYPE: 'FUNCTION',
                    OBJECT_NAME: match[1]
                });
            }
        }
    }
    
    return result;
$$;

-- 执行存储过程获取结果
CALL EXTRACT_TASK_DEPENDENCIES();

方法3：递归查询完整依赖链 #

如果目标存储过程/函数本身还有其他依赖，可结合OBJECT_DEPENDENCIES视图递归查询：

WITH RECURSIVE task_deps AS (
    SELECT
        t.task_name,
        t.schema_name AS task_schema,
        t.database_name AS task_db,
        od.referenced_object_name,
        od.referenced_object_schema,
        od.referenced_object_database,
        od.referenced_object_type
    FROM
        INFORMATION_SCHEMA.TASKS t
    JOIN
        INFORMATION_SCHEMA.OBJECT_DEPENDENCIES od
        ON t.task_name = od.object_name
        AND t.schema_name = od.object_schema
        AND t.database_name = od.object_database
    WHERE
        od.referenced_object_type IN ('PROCEDURE', 'FUNCTION')
    UNION ALL
    SELECT
        td.task_name,
        td.task_schema,
        td.task_db,
        od.referenced_object_name,
        od.referenced_object_schema,
        od.referenced_object_database,
        od.referenced_object_type
    FROM
        task_deps td
    JOIN
        INFORMATION_SCHEMA.OBJECT_DEPENDENCIES od
        ON td.referenced_object_name = od.object_name
        AND td.referenced_object_schema = od.object_schema
        AND td.referenced_object_database = od.object_database
    WHERE
        od.referenced_object_type IN ('PROCEDURE', 'FUNCTION')
)
SELECT DISTINCT
    task_name,
    task_db || '.' || task_schema AS task_full_name,
    referenced_object_database || '.' || referenced_object_schema || '.' || referenced_object_name AS referenced_object_full_name,
    referenced_object_type
FROM
    task_deps
ORDER BY
    task_full_name, referenced_object_type;

任务修改注意事项 #

• 先备份所有任务定义：执行SELECT * FROM INFORMATION_SCHEMA.TASKS并导出为CSV
• 修改任务时使用ALTER TASK <任务名> SET DEFINITION = '<更新后的SQL语句>'，替换存储过程/函数的Schema路径
• 修改后验证任务状态：SHOW TASKS LIKE '<任务名>'，确保任务处于正常状态

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