Snowflake存储过程：在新数据库中克隆现有模式并保留所有权与权限

以下是实现需求的Snowflake JavaScript存储过程，可批量克隆指定数据库下的所有模式到新数据库，同时保留原模式的所有权与权限：

CREATE OR REPLACE PROCEDURE CLONE_ALL_SCHEMAS_FROM_DATAVAULT(TARGET_DB_NAME VARCHAR)
RETURNS VARCHAR
LANGUAGE JAVASCRIPT
AS
$$
    // 步骤1：获取源数据库下的所有模式
    var showSchemasStmt = snowflake.createStatement({
        sqlText: "SHOW SCHEMAS IN DATABASE D_DATAVAULT"
    });
    var schemaResult = showSchemasStmt.execute();
    
    // 步骤2：收集所有模式名称到列表
    var schemaList = [];
    while (schemaResult.next()) {
        schemaList.push(schemaResult.getColumnValue("NAME"));
    }
    
    // 步骤3：循环执行模式克隆语句
    var cloneCount = 0;
    for (var i = 0; i < schemaList.length; i++) {
        var schemaName = schemaList[i];
        try {
            var cloneStmt = snowflake.createStatement({
                sqlText: `CREATE SCHEMA IF NOT EXISTS ${TARGET_DB_NAME}.${schemaName} CLONE D_DATAVAULT.${schemaName}`
            });
            cloneStmt.execute();
            cloneCount++;
        } catch (err) {
            return `克隆模式 ${schemaName} 失败：${err.message}`;
        }
    }
    
    return `成功克隆 ${cloneCount} 个模式到目标数据库 ${TARGET_DB_NAME}`;
$$;

使用说明 #

1. 调用存储过程时传入目标数据库名称，例如：

CALL CLONE_ALL_SCHEMAS_FROM_DATAVAULT('NEW_TARGET_DATABASE');

2. 确保执行存储过程的角色拥有以下权限：
   • 源数据库D_DATAVAULT的USAGE权限，以及所有待克隆模式的USAGE权限
   • 目标数据库的CREATE SCHEMA权限

关键说明 #

• 采用SHOW SCHEMAS获取模式列表，无需依赖额外视图或查询
• 使用CREATE SCHEMA ... CLONE语句确保原模式的所有权、权限及关联对象完整复制
• 加入异常捕获逻辑，遇到克隆失败时直接返回错误信息，便于快速排查问题
• 新增IF NOT EXISTS关键字，避免重复克隆已存在的模式导致报错

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