SqlServer 根据 A 表字段结构创建 B 表或追加字段到 B 表

啪！贴脚本：

declare @sourceSchema varchar(64),   --源表，架构
        @sourceTable varchar(64),    --源表，表名
        @targetSchema varchar(64),   --目标表，架构
        @targetTable varchar(64),    --目标表，表名
        @isAppendMode int,           --是否是追加字段模式，0：创建全新目标表；1:追加表字段模式（目标表必须存在；若有源表主键，仅追加列名）
        @isCopyThorough int          --0：仅字段名和数据类型与源表字段相同，缺省值；1：目标表完全按照源表创建（包括主键、约束等）,

set @sourceSchema = 'dbo'
set @sourceTable = 'tb1'
set @targetSchema = 'dbo'
set @targetTable = 'tb2'
set @isAppendMode = 1
set @isCopyThorough = 1

IF OBJECT_ID(''+@sourceSchema+'.'+@sourceTable+'','U') is null
begin
    select '0' as result, '源表不存在！' as errmsg
    return;
end

if(@isCopyThorough not in(0,1))
begin
    select '0' as result, '参数 @isCopyThorough 值无效！值域应为 0 或 1' as errmsg
    return;
end

--drop table [dbo].[R2_test]

declare @sql varchar(max), @i int, @nCount int,
    @column_name sysname, @data_type sysname, @data_length sysname, 
    @is_identity sysname, @default_value sysname, @is_nullable sysname

--源表所有字段
declare @sourceFileds table(
    [num] int,
    [schema_name] sysname null,
    [table_name] sysname null,
    [column_id] sysname null,
    [column_name] sysname null,
    [data_type] sysname null,
    [data_length] sysname null,
    [is_identity] sysname null,
    [default_value] sysname null,
    [is_nullable] sysname null
)

insert into @sourceFileds select 
    ROW_NUMBER() OVER (order by col.column_id asc) as [num],
    schema_name(tab.schema_id) as [schema_name],
    tab.name as table_name, 
    col.column_id,
    col.name as column_name, 
    t.name as data_type,       
    (case cast(c.CHARACTER_MAXIMUM_LENGTH as varchar(6)) when '-1' then 'max' else cast(c.CHARACTER_MAXIMUM_LENGTH as varchar(6)) end) [data_length],  
       case when exists
       (
          SELECT 1 FROM sysobjects where xtype='PK' and name in
           (
               SELECT name FROM sysindexes WHERE indid in
              (
                  SELECT indid FROM sysindexkeys WHERE id=col.object_id AND colid= col.column_id
              )
           )
       ) then 1 else 0 end is_identity,
       c.COLUMN_DEFAULT default_value,
       col.is_nullable
    from sys.tables as tab 
        inner join sys.columns as col on tab.object_id = col.object_id
        left join sys.types as t on col.user_type_id = t.user_type_id 
        left join INFORMATION_SCHEMA.COLUMNS c on c.COLUMN_NAME = col.name and c.TABLE_NAME = tab.name and c.TABLE_SCHEMA = schema_name(tab.schema_id)
        where schema_name(tab.schema_id)+'.'+tab.name = @sourceSchema+'.'+@sourceTable

--select * from @sourceFileds

--1、创建新表模式、仅字段名和数据类型与源表字段相同
--2、创建新表模式、目标表完全按照源表创建（包括主键、约束等）
if(@isAppendMode=0)
begin
    IF OBJECT_ID(''+@targetSchema+'.'+@targetTable+'','U') is not null
    begin
        select '0' as result, '创建新表模式，待创建表已存在！' as errmsg
        return;
    end

    set @sql='create table ['+@targetSchema+'].['+@targetTable+']( '

    set @i=0
    set @nCount= (select count(*) from @sourceFileds)

    while(@i<@nCount)
    begin
        select @column_name=[column_name], @data_type=[data_type], @data_length=[data_length], 
            @is_identity=[is_identity], @default_value=[default_value], @is_nullable=[is_nullable] 
        from @sourceFileds
        where 
            num=@i+1
    
        set @sql = @sql+ '['+@column_name+'] '+@data_type+' '  --eg. create table dbo.tb( [fd_01] char 
        
        if(isnull(@data_length,'') <> '')
        begin
            set @sql=@sql+'('+@data_length+') '       --eg. create table dbo.tb( [fd_01] char (1) 
        end            
        
        --目标表完全按照源表创建、源表字段是int型主键，创建目标表int自增主键
        if(@isCopyThorough=1 and @is_identity='1' and @data_type='int')
        begin
            --eg. create table dbo.tb( [ID] int identity(1,1) not null constraint [PK_ID] primary key clustered ([ID] asc),
            set @sql=@sql+'identity(1,1) not null constraint [PK_'+@targetTable+'] primary key clustered (['+@column_name+'] asc) ' 
        end
        else                           
        begin
            --print @is_nullable
            if (@is_nullable='1' or (@is_nullable='0' and @isCopyThorough=0))     --字段可为空 || 仅字段名和数据类型与源表字段相同
            begin                                       
                set @sql=@sql+'null '     --eg. create table dbo.tb( [fd_01] char (1) null
            end                                           
            else                                       
            begin                                       
                set @sql=@sql+'not null '     --eg. create table dbo.tb( [fd_01] char (1) not null
    
                --字段默认值
                if(isnull(@default_value,'') <> '')
                begin
                    --eg. create table dbo.tb( [fd_01] char (2) not null constraint [DF_tb_Deleted] default ('T'),
                    set @sql=@sql+'constraint [DF_'+@targetTable+'_'+@column_name+'] default '+@default_value+' '  
                end
            end
        end                                       
    
        set @sql=@sql+','
        set @i=@i+1
    end

    set @sql=@sql+') on [PRIMARY]'
    print @sql

    exec(@sql)

    select '1' as result, '' as errmsg
    return;
end;
--3、追加表字段模式
else if(@isAppendMode=1)
begin
    IF OBJECT_ID(''+@targetSchema+'.'+@targetTable+'','U') is null
    begin
        select '0' as result, '追加表字段模式，目标表不存在！' as errmsg
        return;
    end;

    --需要追加到目标表的字段
    declare @requireAddFileds table(
        [num] int,
        [schema_name] sysname null,
        [table_name] sysname null,
        [column_id] sysname null,
        [column_name] sysname null,
        [data_type] sysname null,
        [data_length] sysname null,
        [is_identity] sysname null,
        [default_value] sysname null,
        [is_nullable] sysname null
    );
    
    with
    targetFields as
    (
        select col.name as column_name 
            from sys.tables as tab 
                inner join 
                    sys.columns as col on tab.object_id = col.object_id
            where schema_name(tab.schema_id)+'.'+tab.name = @targetSchema+'.'+@targetTable  
    )
    
    --取源表、目标表字段差集，结果为目标表中不存在的字段
    insert into @requireAddFileds select ROW_NUMBER() OVER (order by a.column_id asc) as [num], a.[schema_name],
            a.[table_name], a.[column_id], a.[column_name], a.[data_type], a.[data_length], a.[is_identity],
            a.[default_value], a.[is_nullable]
        from @sourceFileds a
        join
            (select column_name from @sourceFileds
                except 
             select column_name from targetFields) b
        on a.column_name=b.column_name order by a.column_id 
    
    --select * from @requireAddFileds
    
    set @i=0
    set @nCount= (select count(*) from @requireAddFileds)
    
    while(@i<@nCount)
    begin
        select @column_name=[column_name], @data_type=[data_type], @data_length=[data_length], 
            @is_identity=[is_identity], @default_value=[default_value], @is_nullable=[is_nullable] 
        from @requireAddFileds 
        where num=@i+1
    
        set @sql = 'alter table ['+@targetSchema+'].['+@targetTable+'] add '+@column_name+' '+@data_type+' '  --eg. alter table [dbo].[tb] add [fd_01] char 
        
        if(isnull(@data_length,'') <> '')
        begin
            set @sql=@sql+'('+@data_length+') '       --eg. alter table [dbo].[tb] add [fd_01] char (1) 
        end                                           
                                                   
        if (@is_nullable='1' or (@is_nullable='0' and @isCopyThorough=0))     --字段可为空 || 仅字段名和数据类型与源表字段相同
        begin                                       
            set @sql=@sql+'null '     --eg. alter table [dbo].[tb] add [fd_01] char (1) null
        end                                           
        else                                       
        begin                                       
            set @sql=@sql+'not null '     --eg. alter table [dbo].[tb] add [fd_01] char (1) not null
    
            if(isnull(@default_value,'') <> '')
            begin
                --eg. alter table [dbo].[tb] add [fd_01] char (2) not null constraint [DF_tb_Deleted] default ('T')
                set @sql=@sql+'constraint [DF_'+@targetTable+'_'+@column_name+'] default '+@default_value+''  
            end
        end
    
        --print @sql;
    
        exec(@sql)
    
        set @i=@i+1
    end
    
    select '1' as result, '' as errmsg
    return;
end
else
begin
    select '0' as result, '参数 @isAppendMode 值无效！值域应为 0 或 1' as errmsg
end