Generate table structure with primary key based on table name

----------------------------------------
--Generate table structure with primary key based on table name
----------------------------------------
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 if cols.xtype in (165,167,173,175,231,239)
Then ' (' + CONVERT (varchar (), Cols.prec) + ') '
When Cols.xtype in (106,108)
Then ' (' + convert (varchar), Cols.prec) + ', ' + CONVERT (varchar (), Cols.scale) + ') '
Else ' end)
+ Case If cols.isnullable = 1 Then ' else ' not NULL ' end
+ char (+) + 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 [email protected] and [email protected]

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 (+) + char (+) + char (9)
+substring (@str, 3,len (@str)-1) + char (+) + char (10)
+ Case @pkstr = "then"
Else ' PRIMARY KEY CLUSTERED (' + char (+) + char (10)
+ char (9) + + @pkstr + char (+) + char (10)
+ char (9) + + ') on [PRIMARY] ' + char (+) + char (10)
End
+ ') '

Print @str

Generate table structure with primary key based on table name