1 sentrepeat int 4 1 下面是存储过程,如期前面已经有记录了,且这字段为空,则入不进去,如果是个空表,则可以入进去 很奇怪。 CREATE proc addcolumn @tablename varchar(30), @colname varchar(30), @coltype varchar(100), @colid int as
declare @colid_max int declare @sql varchar(1000)if not exists(select 1 from sysobjects where name = @tablename and xtype = 'u') begin raiserror 20001 '没有这个表' return -1 end if exists(select 1 from syscolumns where id = object_id(@tablename) and name = @colname) begin raiserror 20002 '这个表已经有这个列了!' return -1 end select @colid_max = max(colid) from syscolumns where id=object_id(@tablename)
if @colid > @colid_max or @colid < 1 set @colid = @colid + 1 set @sql = 'alter table '+@tablename+' add '+@colname+' '+@coltype exec(@sql)
select @colid_max = colid from syscolumns where id = object_id(@tablename) and name = @colname if @@rowcount <> 1 begin raiserror 20003 '加一个新列不成功,请检查你的列类型是否正确' return -1 end EXEC sp_configure 'allow_updates',1 RECONFIGURE WITH OVERRIDE set @sql = 'update syscolumns set colid = -1 where id = object_id('''+@tablename+''') and colid = '+cast(@colid_max as varchar(10)) exec(@sql) set @sql = 'update syscolumns set colid = colid + 1 where id = object_id('''+@tablename+''') and colid >= '+cast(@colid as varchar(10)) exec(@sql)
set @sql = 'update syscolumns set colid = '+cast(@colid as varchar(10))+' where id = object_id('''+@tablename+''') and name = '''+@colname +'''' exec(@sql) EXEC sp_configure 'allow_updates',0 RECONFIGURE WITH OVERRIDE GO
下面是存储过程,如期前面已经有记录了,且这字段为空,则入不进去,如果是个空表,则可以入进去
很奇怪。
CREATE proc addcolumn
@tablename varchar(30),
@colname varchar(30),
@coltype varchar(100),
@colid int
as
declare @colid_max int
declare @sql varchar(1000)if not exists(select 1 from sysobjects
where name = @tablename and xtype = 'u')
begin
raiserror 20001 '没有这个表'
return -1
end if exists(select 1 from syscolumns
where id = object_id(@tablename) and name = @colname)
begin
raiserror 20002 '这个表已经有这个列了!'
return -1
end select @colid_max = max(colid) from syscolumns where id=object_id(@tablename)
if @colid > @colid_max or @colid < 1
set @colid = @colid + 1 set @sql = 'alter table '+@tablename+' add '+@colname+' '+@coltype
exec(@sql)
select @colid_max = colid
from syscolumns where id = object_id(@tablename) and name = @colname
if @@rowcount <> 1
begin
raiserror 20003 '加一个新列不成功,请检查你的列类型是否正确'
return -1
end EXEC sp_configure 'allow_updates',1 RECONFIGURE WITH OVERRIDE
set @sql = 'update syscolumns
set colid = -1
where id = object_id('''+@tablename+''')
and colid = '+cast(@colid_max as varchar(10))
exec(@sql)
set @sql = 'update syscolumns
set colid = colid + 1
where id = object_id('''+@tablename+''')
and colid >= '+cast(@colid as varchar(10))
exec(@sql)
set @sql = 'update syscolumns
set colid = '+cast(@colid as varchar(10))+'
where id = object_id('''+@tablename+''')
and name = '''+@colname +''''
exec(@sql)
EXEC sp_configure 'allow_updates',0 RECONFIGURE WITH OVERRIDE
GO
信息的系统表不止它一个,这样很容易造成数据库的一致性错误。如果要不在最后的位置增加新列,建议先删除表的外键(如果有),把数据导入临时表
删除原表,再新建表,然后把临时表的数据导回来,最后把删除的外键加上,最后删除临时表