SQLserver中如何通过查询系统表拼接出创建表的SQL语句

在工作中，遇到这样的需求，在一个目标库创建源库中的一个表，并且是通过ssis包传过去，不能通过复制源库中创建表的语句。

那么只能将创建表的语句拼接出来，赋值一个变量，然后将这个变量在目标库中执行以下，就可以在目标库中创建出来新表。

SQL如下：

declare @sql varchar(8000),@tablename varchar(100)

set @tablename = 'test_11'     --这里输入表名

set @sql = 'create table ['+@tablename+'] ('

select @sql = @sql + b.name + ' '+    --取出字段的名字
       c.name+                          --取出字段类型的名称
       case when c.collation_name is not null then '('+
         case when b.max_length <>-1 then convert(varchar(100),b.max_length)
           else 'MAX'
         end +') '
        else ''
       end +                         --取出字段类型的长度
       case when b.is_identity = 1 then ' identity('+convert(varchar(100),IDENT_SEED(@tablename))+','+convert(varchar(100),IDENT_INCR(@tablename))+')' else '' end +
       case when d.definition is not null then ' default('+d.definition +')' else '' end +
       case when b.is_nullable = 0 then ' not null' else ' null' end + ','--是否自增字段，如果是处理一下。
from sys.objects a join sys.columns b
on a.object_id = b.object_id
join sys.types c
on b.system_type_id = c.system_type_id and b.user_type_id = c.user_type_id
left join sys.default_constraints d
on b.default_object_id = d.object_id
where a.name=@tablename
order by b.column_id

if exists(select * from sys.indexes where object_id =object_id(@tablename) and is_primary_key =1 )--判断是否有索引，有就执行上面的拼接语句；没有索引就执行else的语句

begin

select @sql = @sql + 'CONSTRAINT ['+name+'] PRIMARY KEY '+type_desc+'
(' from sys.indexes where object_id =object_id(@tablename) and is_primary_key =1
select @sql = @sql + b.name + case when a.is_descending_key =1 then ' DESC ' else ' ASC ' end +',
' from sys.index_columns a join sys.columns b
on a.object_id= b.object_id and a.column_id = b.column_id
where a.object_id =object_id(@tablename)
select @sql = left(@sql,len(@sql)-3)+'
)'
select @sql = @sql+') ON [PRIMARY] '
end
else
begin
select @sql = left(@sql,len(@sql)-1)+'
) ON [PRIMARY] '
end

select  @sql
 

最后结果是：

create table [test_11] (statdate varchar(8)  null,branch_no varchar(6)  null,target_id varchar(10)  null,target_value decimal null,stamp datetime null  ) ON [PRIMARY]