declare @Table_Name varchar(500)
declare @strSQL varchar(500)
set @Table_Name='UserInfo' --在此处设置要创建的表
if(not exists(SELECT * FROM dbo.SysObjects WHERE ID = object_id(N''+@Table_Name+'') AND OBJECTPROPERTY(ID, 'IsTable') = 1))
begin
SET @strSQL='create table '+@Table_Name+' ( id int IDENTITY(1,1) primary key )'
exec (@strSQL)
end
if(not exists(select * from syscolumns where id=object_id(''+@Table_Name+'') and name='id'))
begin
SET @strSQL='alter table '+@Table_Name+' add id int IDENTITY(1,1) primary key '
exec (@strSQL)
end
if(not exists(select * from syscolumns where id=object_id(''+@Table_Name+'') and name='CreateDate'))
begin
SET @strSQL=' alter table '+@Table_Name+' add CreateDate datetime'
exec (@strSQL)
end
if(not exists(select * from syscolumns where id=object_id(''+@Table_Name+'') and name='CreateUserID'))
begin
SET @strSQL=' alter table '+@Table_Name+' add CreateUserID int'
exec (@strSQL)
end
if(not exists(select * from syscolumns where id=object_id(''+@Table_Name+'') and name='CreateUser'))
begin
SET @strSQL=' alter table '+@Table_Name+' add CreateUser varchar(2000) '
exec (@strSQL)
end
if(not exists(select * from syscolumns where id=object_id(''+@Table_Name+'') and name='LastUpdateUserID'))
begin
SET @strSQL=' alter table '+@Table_Name+' add LastUpdateUserID int '
exec (@strSQL)
end
if(not exists(select * from syscolumns where id=object_id(''+@Table_Name+'') and name='LastUpdateDate'))
begin
SET @strSQL=' alter table '+@Table_Name+' add LastUpdateDate datetime'
exec (@strSQL)
end
if(not exists(select * from syscolumns where id=object_id(''+@Table_Name+'') and name='LastUpdateUser'))
begin
SET @strSQL=' alter table '+@Table_Name+' add LastUpdateUser varchar(2000)'
exec (@strSQL)
end
if(not exists(select * from syscolumns where id=object_id(''+@Table_Name+'') and name='LastUpTimestamp'))
begin
SET @strSQL=' alter table '+@Table_Name+' add LastUpTimestamp timestamp'
exec (@strSQL)
end
if(not exists(select * from syscolumns where id=object_id(''+@Table_Name+'') and name='SortIndex'))
begin
SET @strSQL=' alter table '+@Table_Name+' add SortIndex int '
exec (@strSQL)
end
if(not exists(select * from syscolumns where id=object_id(''+@Table_Name+'') and name='HotIndex'))
begin
SET @strSQL=' alter table '+@Table_Name+' add HotIndex int '
exec (@strSQL)
end
if(not exists(select * from syscolumns where id=object_id(''+@Table_Name+'') and name='IsAudit'))
begin
SET @strSQL=' alter table '+@Table_Name+' add IsAudit int '
exec (@strSQL)
end
if(not exists(select * from syscolumns where id=object_id(''+@Table_Name+'') and name='AuditDate'))
begin
SET @strSQL=' alter table '+@Table_Name+' add AuditDate datetime '
exec (@strSQL)
end
if(not exists(select * from syscolumns where id=object_id(''+@Table_Name+'') and name='IsHot'))
begin
SET @strSQL=' alter table '+@Table_Name+' add IsHot int'
exec (@strSQL)
end
if(not exists(select * from syscolumns where id=object_id(''+@Table_Name+'') and name='IsDel'))
begin
SET @strSQL=' alter table '+@Table_Name+' add IsDel int '
exec (@strSQL)
end
if(not exists(select * from syscolumns where id=object_id(''+@Table_Name+'') and name='DelDate'))
begin
SET @strSQL=' alter table '+@Table_Name+' add DelDate datetime '
exec (@strSQL)
end
if(not exists(select * from syscolumns where id=object_id(''+@Table_Name+'') and name='IsEnable'))
begin
SET @strSQL=' alter table '+@Table_Name+' add IsEnable int '
exec (@strSQL)
end
if(not exists(select * from syscolumns where id=object_id(''+@Table_Name+'') and name='EnableDate'))
begin
SET @strSQL=' alter table '+@Table_Name+' add EnableDate datetime '
exec (@strSQL)
end
if(not exists(select * from syscolumns where id=object_id(''+@Table_Name+'') and name='Status'))
begin
SET @strSQL=' alter table '+@Table_Name+' add Status int '
exec (@strSQL)
end
if(not exists(select * from syscolumns where id=object_id(''+@Table_Name+'') and name='Pid'))
begin
SET @strSQL=' alter table '+@Table_Name+' add Pid int'
exec (@strSQL)
end
go
创建数据库表默认字段封装SQL
最新推荐文章于 2021-11-08 23:02:02 发布