根据表名 生成Create创建脚本

declare @sql varchar(8000),@tablename varchar(100)set @tablename ='test'--这里输入表名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 bon a.object_id = b.object_idjoin sys.types con b.system_type_id = c.system_type_id and b.user_type_id = c.user_type_idleft join sys.default_constraints don b.default_object_id = d.object_idwhere a.name =@tablenameorder by b.column_idif exists(select * from sys.indexes where object_id =object_id(@tablename) and is_primary_key =1 )beginselect @sql = @sql + 'CONSTRAINT ['+name+'] PRIMARY KEY '+type_desc+'(' from sys.indexes where object_id =object_id(@tablename) and is_primary_key =1select @sql = @sql + b.name + case when a.is_descending_key =1 then ' DESC' else ' ASC' end +',' from sys.index_columns a join sys.columns bon a.object_id= b.object_id and a.column_id = b.column_idwhere a.object_id =object_id(@tablename)select @sql = left(@sql,len(@sql)-3)+')'select @sql = @sql+') ON [PRIMARY] 'endelsebeginselect @sql = left(@sql,len(@sql)-1)+') ON [PRIMARY] 'endprint @sql