create proc 改列的顺序 @tablename varchar(255), @colname varchar(30), --要加的列名 @colid int --加到第几列 as declare @colid_max int declare @sql varchar(1000) --动态sql语句--保证该表的colid是连续的 select @colid_max = max(colid) from syscolumns where id=object_id(@tablename)if @colid > @colid_max or @colid < 1 set @colid = @colid_max + 1--打开修改系统表的开关 EXEC sp_configure 'allow updates',1 RECONFIGURE WITH OVERRIDE--将新列列号暂置为-1 set @sql = 'update syscolumns set colid = -1 where id = object_id('''+@tablename+''') and colid = '+cast(@colid_max as varchar(10)) exec(@sql)--将其他列的列号加1 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 table test(a int,b as a+1) go exec 改列的顺序 'test',b,1 go select * from test
ALTER TABLE 表 alter CONSTRAINT ....
create proc 改列的顺序 @tablename varchar(255), @colname varchar(30), --要加的列名 @colid int --加到第几列 as declare @colid_max int,@old_id int declare @sql varchar(1000) --动态sql语句--保证该表的colid是连续的 select @colid_max = max(colid) from syscolumns where id=object_id(@tablename) select @old_id=colid from syscolumns where id = object_id(@tablename) and name = @colname if @colid > @colid_max or @colid < 1 set @colid = @colid_max + 1--打开修改系统表的开关 EXEC sp_configure 'allow updates',1 RECONFIGURE WITH OVERRIDE--将新列列号暂置为-1 set @sql = 'update syscolumns set colid = -1 where id = object_id('''+@tablename+''') and colid = '+cast(@colid_max as varchar(10)) exec(@sql)--将其他列的列号加1 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)--改计算列 set @sql = 'update syscomments set number='+cast(@colid as varchar(10))+' where number='+cast(@old_id as varchar(10))+' and id=object_id('''+@tablename+''')' exec(@sql) -------------------------------------------------- --关闭修改系统表的开关 EXEC sp_configure 'allow updates',0 RECONFIGURE WITH OVERRIDE go ----------------测试:create table test(a int,b as a+1) go exec 改列的顺序 'test',b,1 go select * from test
--完美版,可以任意指定是向前移还是向后移 create proc 改列的顺序 @tablename varchar(255), @colname varchar(30), --要加的列名 @newid int --加到第几列 as declare @newid_max int,@old_id int declare @sql varchar(1000) --动态sql语句--保证该表的colid是连续的 select @newid_max = max(colid) from syscolumns where id=object_id(@tablename) select @old_id=colid from syscolumns where id = object_id(@tablename) and name = @colname if @newid > @newid_max or @newid < 1 set @newid = @newid_max+1--打开修改系统表的开关 EXEC sp_configure 'allow updates',1 RECONFIGURE WITH OVERRIDE--将旧列列号暂置为-1 set @sql = 'update syscolumns set colid = -1 where id = object_id('''+@tablename+''') and colid = '+cast(@old_id as varchar(10)) exec(@sql)--将旧计算列号置为-1 set @sql = 'update syscomments set number=-1 where number='+cast(@old_id as varchar(10))+' and id=object_id('''+@tablename+''')' exec(@sql) if @old_id>=@newid begin--将其他列的列号加1 set @sql = 'update syscolumns set colid = colid+1 where id = object_id('''+@tablename+''') and colid between '+cast(@newid as varchar(10))+' and '+cast(@old_id as varchar(10)) exec(@sql) --将其他计算列加1 set @sql = 'update syscomments set number=number+1 where id=object_id('''+@tablename+''') and number between '+cast(@newid as varchar(10))+' and '+cast(@old_id as varchar(10)) exec(@sql)end else begin --将其他列的列号加1 set @sql = 'update syscolumns set colid=colid-1 where id=object_id('''+@tablename+''') and colid between '+cast(@old_id as varchar(10))+' and '+cast(@newid as varchar(10)) exec(@sql) --将其他计算列加1 set @sql = 'update syscomments set number=number-1 where id=object_id('''+@tablename+''') and number between '+cast(@old_id as varchar(10))+' and '+cast(@newid as varchar(10)) exec(@sql) end--将新列列号复位 set @sql = 'update syscolumns set colid = '+cast(@newid as varchar(10))+' where id = object_id('''+@tablename+''') and colid=-1' exec(@sql)--改计算列 set @sql = 'update syscomments set number='+cast(@newid as varchar(10))+' where number=-1 and id=object_id('''+@tablename+''')' exec(@sql) -------------------------------------------------- --关闭修改系统表的开关 EXEC sp_configure 'allow updates',0 RECONFIGURE WITH OVERRIDE go ----------------测试:create table test(a int,b as a+100,c as a-100) insert test (a) values(2323) go exec 改列的顺序 'test','b',1 go select * from test exec 改列的顺序 'test','b',1 go select * from testdrop table test
ALTER TABLE 表 DROP COLUMN 列--加
ALTER TABLE 表 ADD 列 as a-b
ALTER TABLE 表 ADD 列 as a*b
alter table 表 add 列 as 列a-列b
如果换位置会引起程序出错
看来只好用SQL管理器调整了
因为程序里用的是这个表
一样要影响程序的说
我已经用管理器弄好了
只不过要同步多个数据库
这么做真是TMD麻烦
@tablename varchar(255),
@colname varchar(30), --要加的列名
@colid int --加到第几列
as
declare @colid_max int
declare @sql varchar(1000) --动态sql语句--保证该表的colid是连续的
select @colid_max = max(colid) from syscolumns where id=object_id(@tablename)if @colid > @colid_max or @colid < 1
set @colid = @colid_max + 1--打开修改系统表的开关
EXEC sp_configure 'allow updates',1 RECONFIGURE WITH OVERRIDE--将新列列号暂置为-1
set @sql = 'update syscolumns
set colid = -1
where id = object_id('''+@tablename+''')
and colid = '+cast(@colid_max as varchar(10))
exec(@sql)--将其他列的列号加1
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 table test(a int,b as a+1)
go
exec 改列的顺序 'test',b,1
go
select * from test
@tablename varchar(255),
@colname varchar(30), --要加的列名
@colid int --加到第几列
as
declare @colid_max int,@old_id int
declare @sql varchar(1000) --动态sql语句--保证该表的colid是连续的
select @colid_max = max(colid) from syscolumns where id=object_id(@tablename)
select @old_id=colid from syscolumns where id = object_id(@tablename) and name = @colname
if @colid > @colid_max or @colid < 1
set @colid = @colid_max + 1--打开修改系统表的开关
EXEC sp_configure 'allow updates',1 RECONFIGURE WITH OVERRIDE--将新列列号暂置为-1
set @sql = 'update syscolumns
set colid = -1
where id = object_id('''+@tablename+''')
and colid = '+cast(@colid_max as varchar(10))
exec(@sql)--将其他列的列号加1
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)--改计算列
set @sql = 'update syscomments set number='+cast(@colid as varchar(10))+' where number='+cast(@old_id as varchar(10))+' and id=object_id('''+@tablename+''')'
exec(@sql)
--------------------------------------------------
--关闭修改系统表的开关
EXEC sp_configure 'allow updates',0 RECONFIGURE WITH OVERRIDE
go
----------------测试:create table test(a int,b as a+1)
go
exec 改列的顺序 'test',b,1
go
select * from test
新加列之后的计算列定义就没了
莫非要象update表syscolumns一样来修改表syscomments
create proc 改列的顺序
@tablename varchar(255),
@colname varchar(30), --要加的列名
@newid int --加到第几列
as
declare @newid_max int,@old_id int
declare @sql varchar(1000) --动态sql语句--保证该表的colid是连续的
select @newid_max = max(colid) from syscolumns where id=object_id(@tablename)
select @old_id=colid from syscolumns where id = object_id(@tablename) and name = @colname
if @newid > @newid_max or @newid < 1
set @newid = @newid_max+1--打开修改系统表的开关
EXEC sp_configure 'allow updates',1 RECONFIGURE WITH OVERRIDE--将旧列列号暂置为-1
set @sql = 'update syscolumns
set colid = -1
where id = object_id('''+@tablename+''')
and colid = '+cast(@old_id as varchar(10))
exec(@sql)--将旧计算列号置为-1
set @sql = 'update syscomments set number=-1 where number='+cast(@old_id as varchar(10))+' and id=object_id('''+@tablename+''')'
exec(@sql)
if @old_id>=@newid
begin--将其他列的列号加1
set @sql = 'update syscolumns set colid = colid+1 where id = object_id('''+@tablename+''') and colid between '+cast(@newid as varchar(10))+' and '+cast(@old_id as varchar(10))
exec(@sql)
--将其他计算列加1
set @sql = 'update syscomments set number=number+1 where id=object_id('''+@tablename+''') and number between '+cast(@newid as varchar(10))+' and '+cast(@old_id as varchar(10))
exec(@sql)end
else
begin
--将其他列的列号加1
set @sql = 'update syscolumns set colid=colid-1 where id=object_id('''+@tablename+''') and colid between '+cast(@old_id as varchar(10))+' and '+cast(@newid as varchar(10))
exec(@sql)
--将其他计算列加1
set @sql = 'update syscomments set number=number-1 where id=object_id('''+@tablename+''') and number between '+cast(@old_id as varchar(10))+' and '+cast(@newid as varchar(10))
exec(@sql)
end--将新列列号复位
set @sql = 'update syscolumns set colid = '+cast(@newid as varchar(10))+' where id = object_id('''+@tablename+''') and colid=-1'
exec(@sql)--改计算列
set @sql = 'update syscomments set number='+cast(@newid as varchar(10))+' where number=-1 and id=object_id('''+@tablename+''')'
exec(@sql)
--------------------------------------------------
--关闭修改系统表的开关
EXEC sp_configure 'allow updates',0 RECONFIGURE WITH OVERRIDE
go
----------------测试:create table test(a int,b as a+100,c as a-100)
insert test (a) values(2323)
go
exec 改列的顺序 'test','b',1
go
select * from test
exec 改列的顺序 'test','b',1
go
select * from testdrop table test