能否通过procedure调用实现数据库复制？USE语句执行问题求助

问题根源 #

USE 是会话级别的语句，动态SQL的执行上下文是独立的临时批处理，执行后不会改变当前会话的数据库上下文，所以在动态SQL中调用USE无法实现会话级别的数据库切换。

可行实现方案 #

核心思路是避免依赖USE语句切换数据库，直接在对象创建脚本中指定目标数据库名称，同时明确告知调用者需手动切换数据库（或由应用层处理）。

步骤1：编写存储过程实现全对象复制

以下是SQL Server环境下的示例存储过程，支持传入源库名和新库名，自动复制表、存储过程、函数等对象：

CREATE PROCEDURE CopyDatabase
    @SourceDB NVARCHAR(128),
    @NewDB NVARCHAR(128)
AS
BEGIN
    SET NOCOUNT ON;

    -- 1. 创建目标数据库
    DECLARE @CreateDBCmd NVARCHAR(MAX) = N'CREATE DATABASE [' + @NewDB + N']';
    EXEC sys.sp_executesql @CreateDBCmd;

    -- 2. 复制表结构与数据
    DECLARE @TableName NVARCHAR(128);
    DECLARE TableCursor CURSOR FOR
        SELECT name FROM [' + @SourceDB + N'].sys.tables WHERE type = 'U';

    OPEN TableCursor;
    FETCH NEXT FROM TableCursor INTO @TableName;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        -- 复制表结构
        DECLARE @CreateTableCmd NVARCHAR(MAX) = N'
            SELECT * INTO [' + @NewDB + N'].dbo.' + QUOTENAME(@TableName) + N'
            FROM [' + @SourceDB + N'].dbo.' + QUOTENAME(@TableName) + N' WHERE 1=0;
        ';
        EXEC sys.sp_executesql @CreateTableCmd;

        -- 插入数据
        DECLARE @InsertDataCmd NVARCHAR(MAX) = N'
            INSERT INTO [' + @NewDB + N'].dbo.' + QUOTENAME(@TableName) + N'
            SELECT * FROM [' + @SourceDB + N'].dbo.' + QUOTENAME(@TableName) + N';
        ';
        EXEC sys.sp_executesql @InsertDataCmd;

        FETCH NEXT FROM TableCursor INTO @TableName;
    END

    CLOSE TableCursor;
    DEALLOCATE TableCursor;

    -- 3. 复制存储过程、函数等可编程对象
    DECLARE @ObjType CHAR(2), @ObjName NVARCHAR(128);
    DECLARE ObjCursor CURSOR FOR
        SELECT type, name FROM [' + @SourceDB + N'].sys.objects 
        WHERE type IN ('P', 'FN', 'IF', 'TF') -- 覆盖存储过程、各类函数
        AND is_ms_shipped = 0;

    OPEN ObjCursor;
    FETCH NEXT FROM ObjCursor INTO @ObjType, @ObjName;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        -- 获取对象创建脚本
        DECLARE @CreateScript NVARCHAR(MAX);
        EXEC [' + @SourceDB + N'].sys.sp_helptext @ObjName, @CreateScript OUTPUT;

        -- 替换脚本中的源库引用为目标库
        SET @CreateScript = REPLACE(@CreateScript, QUOTENAME(@SourceDB), QUOTENAME(@NewDB));

        -- 在目标库中创建对象
        DECLARE @ExecScript NVARCHAR(MAX) = N'USE [' + @NewDB + N']; ' + @CreateScript;
        EXEC sys.sp_executesql @ExecScript;

        FETCH NEXT FROM ObjCursor INTO @ObjType, @ObjName;
    END

    CLOSE ObjCursor;
    DEALLOCATE ObjCursor;

    -- 提示调用者手动切换数据库
    PRINT N'数据库复制完成，请执行 USE [' + @NewDB + N']; 切换至目标数据库。';
END

步骤2：处理数据库切换需求

由于存储过程内无法通过动态SQL修改当前会话的数据库上下文，有两种处理方式：

• 手动切换：存储过程执行完成后，调用者直接执行USE [新库名];语句。
• 应用层处理：如果是在应用程序中调用存储过程，可在存储过程执行完毕后，由应用程序向数据库发送USE语句完成切换。

注意事项 #

• 示例仅覆盖了常见对象类型，实际使用时需扩展处理视图、触发器、索引、约束、权限等对象。
• 若源库对象存在跨库引用，需额外处理脚本中的数据库名称替换逻辑。
• 执行存储过程的账号需具备源库的读取权限和目标库的创建权限。

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