ALTER TABLE doc_exa ADD column_b VARCHAR(20) NULL
解决方案 »
- 在应使用条件的上下文(在 'ORDER' 附近)中指定了非布尔类型的表达式。
- SQL 记录系统顺号
- 如何联合三个表查询出想要的结果呢?小菜鸟求救,谢谢!
- 高手请进,master..xp_cmdshell 使用问题!
- 我的SQL语句里需要牵扯到5个表,太长了,有什么办法可以简化呀,
- 如何恢复sql server 被误删的数据
- 如何在sql2000 中算地图中两点坐标距离 急等!
- 求助,我怎么从数据库里取不出这个字段来呢
- 关于生成sql server 2000无值守安装文件的问题。生成后怎也找不着有.iss文件。很奇怪已经试过无数次了。
- oracle的有关问题?
- 请哥哥姐姐帮忙回这个帖子,谢谢。
- 如何在存储过程中将一个文件存储到image,ntext列中呢?
drop table table
select col1,col2,'' as NewCol,col3..... into table from #temp
drop table #temp
update syscolumns set colid = 4 where name = 'id2' and id = 2066106401
update syscolumns set colid = 2 where name = 'id3' and id = 2066106401
update syscolumns set colid = 3 where name = 'id2' and id = 2066106401
EXEC sp_configure 'allow updates',0 RECONFIGURE WITH OVERRIDEselect * from testco
update syscolumns set colid = 4 where name = 'id2' and id = object_id('testco')
update syscolumns set colid = 2 where name = 'id3' and id = object_id('testco')
update syscolumns set colid = 3 where name = 'id2' and id = object_id('testco')
EXEC sp_configure 'allow updates',0 RECONFIGURE WITH OVERRIDEselect * from testco
我按你写的试了一下运行后系统提示:未启用对系统目录的特殊更新。系统管理员必须重新配置 SQL Server 以允许这种操作。
请问怎么回这样???
create table testco (id1 int,id2 int)alter table testco add id3 int
go
EXEC sp_configure 'allow updates',1 RECONFIGURE WITH OVERRIDE
go
update syscolumns set colid = 4 where name = 'id2' and id = object_id('testco')
update syscolumns set colid = 2 where name = 'id3' and id = object_id('testco')
update syscolumns set colid = 3 where name = 'id2' and id = object_id('testco')
go
EXEC sp_configure 'allow updates',0 RECONFIGURE WITH OVERRIDE
where name = ''id2'' and id = object_id(''testco'')')
exec('update syscolumns set colid = 2
where name = ''id3'' and id = object_id(''testco'')')
exec('update syscolumns set colid = 3
where name = ''id2'' and id = object_id(''testco'')')EXEC sp_configure 'allow updates',0 RECONFIGURE WITH OVERRIDEselect * from testco
已经可以插入字段了!!!谢谢!!!
但是数据不对了???还是不能用呀
好的!谢谢!!!
@tablename varchar(30), --表名
@colname varchar(30), --要加的列名
@coltype varchar(100), --要加的列类型
@colid int --加到第几列
asdeclare @colid_max int
declare @sql varchar(1000) --动态sql语句
--------------------------------------------------
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
--------------------------------------------------
--保证该表的colid是连续的
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 @colid_max is null
begin
raiserror 20003 '加一个新列不成功,请检查你的列类型是否正确'
return -1
end
--------------------------------------------------
--打开修改系统表的开关
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
调用方法:
exec addcolumn '表名','新列名','新列类型',加到第几个位置
如:
exec addcolumn 'test','id2','char(10)',2
表示将id2这个列加到表test的第二个位置,类型是char(10)。
create proc addcolumn
@tablename varchar(30), --表名
@colname varchar(30), --要加的列名
@coltype varchar(100), --要加的列类型
@colid int --加到第几列
asdeclare @colid_max int
declare @sql varchar(1000) --动态sql语句
--------------------------------------------------
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
--------------------------------------------------
--保证该表的colid是连续的
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--将新列列号暂置为-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
调用方法:
exec addcolumn '表名','新列名','新列类型',加到第几个位置
如:
exec addcolumn 'test','id2','char(10)',2
表示将id2这个列加到表test的第二个位置,类型是char(10)。
这位大哥(大姐)不好意思,好象还是不行,数据丢失了。
id1 id2
1 2
3 4
5 6
7 8
9 0
正确的:
id1 id3 id2
1 NULL 2
3 NULL 4
5 NULL 6
.
.
使用Chiff(~o~) 的存储过程
测试结果正确select * into #tTemp from tablename
exec addcolumn 'tablename','testname2','varchar',2
go
delete tablename
insert tablename select [colname1],'',[colname.....字段列表] from #tTemp
drop table #tTemp