如何生成脚本在不同schema下自动创建源schema所有表

报错原因说明 #

你遇到的报错是因为SELECT * INTO语法会默认继承源表的索引属性，若源表存在列存储索引，且表中包含不支持列存储索引的数据类型（如text、ntext、image、非二进制CLR类型等），就会触发该错误。通过先建表再插入数据的逻辑避开了自动继承索引的逻辑，所以可以正常执行。

实现逻辑 #

• 动态遍历源Schema下所有用户表，排除系统表
• 调用系统视图生成目标表的精准建表语句，完全复刻字段类型、长度、非空约束、默认值等属性，不继承源表索引（可按需调整是否同步索引）
• 支持每日全量备份场景，先删除目标Schema下已存在的同名表后再重建
• 建表完成后批量插入全量数据

完整存储过程代码 #

CREATE PROCEDURE dbo.SyncSchemaTables
    @SourceSchema NVARCHAR(128), -- 源Schema名，示例值：'dbo'
    @TargetSchema NVARCHAR(128)  -- 目标Schema名，示例值：'dbo_b'
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @TableName NVARCHAR(128);
    DECLARE @CreateTableSQL NVARCHAR(MAX);
    DECLARE @InsertSQL NVARCHAR(MAX);

    -- 收集源Schema下所有用户表
    DECLARE @TableList TABLE (TableName NVARCHAR(128));
    INSERT INTO @TableList(TableName)
    SELECT name FROM sys.tables 
    WHERE schema_id = SCHEMA_ID(@SourceSchema) AND type = 'U';

    -- 遍历所有表执行同步
    DECLARE table_cursor CURSOR FOR SELECT TableName FROM @TableList;
    OPEN table_cursor;
    FETCH NEXT FROM table_cursor INTO @TableName;
    WHILE @@FETCH_STATUS = 0
    BEGIN
        -- 全量备份场景：删除目标端已存在的同名表，如需保留历史可注释这段
        IF EXISTS (
            SELECT 1 FROM sys.tables 
            WHERE schema_id = SCHEMA_ID(@TargetSchema) AND name = @TableName
        )
        BEGIN
            EXEC sp_executesql N'DROP TABLE ' + QUOTENAME(@TargetSchema) + N'.' + QUOTENAME(@TableName);
        END

        -- 生成建表语句，完全复刻字段属性，不包含索引
        SELECT @CreateTableSQL = N'CREATE TABLE ' + QUOTENAME(@TargetSchema) + N'.' + QUOTENAME(@TableName) + N' (' + 
            STRING_AGG(
                QUOTENAME(c.name) + N' ' + t.name + 
                CASE WHEN t.name IN ('varchar','nvarchar','char','nchar') 
                    THEN N'(' + CASE WHEN c.max_length = -1 THEN N'MAX' ELSE CAST(c.max_length/CASE WHEN t.name IN ('nvarchar','nchar') THEN 2 ELSE 1 END AS NVARCHAR) END + N')' 
                    ELSE N'' END +
                CASE WHEN c.is_nullable = 0 THEN N' NOT NULL' ELSE N' NULL' END +
                CASE WHEN d.definition IS NOT NULL THEN N' DEFAULT ' + d.definition ELSE N'' END
            , N', ')
        + N');'
        FROM sys.columns c
        JOIN sys.types t ON c.system_type_id = t.system_type_id AND c.user_type_id = t.user_type_id
        LEFT JOIN sys.default_constraints d ON c.default_object_id = d.object_id AND c.object_id = d.parent_object_id
        WHERE c.object_id = OBJECT_ID(QUOTENAME(@SourceSchema) + N'.' + @TableName)
        ORDER BY c.column_id;

        -- 执行建表
        EXEC sp_executesql @CreateTableSQL;

        -- 插入全量数据
        SET @InsertSQL = N'INSERT INTO ' + QUOTENAME(@TargetSchema) + N'.' + QUOTENAME(@TableName) + N' SELECT * FROM ' + QUOTENAME(@SourceSchema) + N'.' + QUOTENAME(@TableName);
        EXEC sp_executesql @InsertSQL;

        FETCH NEXT FROM table_cursor INTO @TableName;
    END
    CLOSE table_cursor;
    DEALLOCATE table_cursor;
END

使用方法 #

• 存储过程创建完成后，直接执行即可完成单次同步：

EXEC dbo.SyncSchemaTables @SourceSchema = 'dbo', @TargetSchema = 'dbo_b'

• 如需每日自动执行，只需在SQL Server代理中创建定时作业，调用上述存储过程即可

可选调整项 #

• 如需同步主键、普通索引等属性，可在生成建表语句后追加对应索引创建逻辑，通过sys.indexes、sys.index_columns系统视图生成索引语句
• 如需增量同步，可删除DROP表逻辑，增加数据比对、增量插入/更新逻辑，避免全量删除重建的性能损耗

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