根据表名生成表结构 含 主键
根据表名生成表结构 含 主键
---------------------------------------- -- 根据表名生成表结构 含 主键 ---------------------------------------- declare @TableName varchar(255) set @TableName = 'bao_color' --'company' ---------------------------------------- declare @str varchar(max) set @str = '' select @str = @str + char(9) +',[' + cols.name + '] ' + type.name + ( case when cols.xtype in (165,167,173,175,231,239) then '('+ convert(varchar(10) , cols.prec) + ')' when cols.xtype in (106,108) then '('+ convert(varchar(10) , cols.prec)+' , ' + convert(varchar(10) ,cols.scale)+ ')' else '' end ) + case when cols.isnullable = 1 then '' else ' not null ' end + char(13) + char(10) from syscolumns cols inner join systypes type on cols.xtype = type.xtype and cols.xtype = type.xusertype where cols.id = (select id from sysobjects where name = @TableName) Declare @objectid int , @pkstr varchar(max) Set @objectid=object_id(@TableName) set @pkstr = '' Select @pkstr = @pkstr + col_name(@objectid,colid) + ' ASC,' From sysobjects as o Inner Join sysindexes as i On i.name=o.name Inner Join sysindexkeys as k On k.indid=i.indid Where o.xtype = 'PK' and parent_obj=@objectid and k.id=@objectid set @pkstr = ISNULL(@pkstr ,'') set @pkstr = case when @pkstr = '' then '' else SUBSTRING(@pkstr,1, LEN(@pkstr)-1) end set @str = 'create table dbo.' + @TableName + '('+ char(13) + char(10) + char(9) +substring(@str,3,len(@str)-1) + char(13) + char(10) + case when @pkstr='' then '' else ' PRIMARY KEY CLUSTERED ( '+ char(13) + char(10) + char(9) ++ @pkstr + char(13) + char(10) + char(9) ++ ')ON [PRIMARY]'+ char(13) + char(10) end + ')' print @str