alter table employee add test1 int null go sp_configure 'allow updates',1 RECONFIGURE WITH override go UPDATE syscolumns SET colid=colid+1 WHERE id=object_id('employee') update syscolumns set colid=1 where id=object_id('employee') and name='test1' go sp_configure 'allow updates',0 RECONFIGURE WITH override
原表: create table Tbb(aa nchar(10) NULL,a int NULL,b varchar(100) NULL) insert into Tbb select 1,1,1 insert into Tbb select 2,2,2--增长一列ff,使之位于第一列: CREATE TABLE dbo.Tmp_Tbb ( ff nchar(10) NULL, aa nchar(10) NULL, a int NULL, b varchar(100) NULL ) ON [PRIMARY] GO IF EXISTS(SELECT * FROM dbo.Tbb) EXEC('INSERT INTO dbo.Tmp_Tbb (aa, a, b) SELECT aa, a, b FROM dbo.Tbb WITH (HOLDLOCK TABLOCKX)') GO DROP TABLE dbo.Tbb GO EXECUTE sp_rename N'dbo.Tmp_Tbb', N'Tbb' GO
13楼是sql server管理界面执行原理。
新建一个临时表,然后再把原表删除再rename临时表就可以了!!
--当一个栏位被删除后,重新建立后,其位置不在原来的地方,如果想恢复该栏位在表中的出现的顺序,需更 --改栏位在syscolumns的colid值,其具体步骤如下: create table test_column(a char(1) null,b char(2) null,c char(1) null,d char(2)null)--栏位被删除前的顺序 select * from test_column alter table test_column drop column c--栏位被删除后的顺序select * from test_column alter table test_column add c varchar(1) null--栏位被添加后的顺序 select * from test_column--更改栏位的顺序:sp_configure 'allow updates','1' go reconfigure with override update syscolumns set colid=colid+1 where colid>3 and id=object_id('test_column') update syscolumns set colid=3 where name='c' and id=object_id('test_column') sp_configure 'allow updates','0' go reconfigure with override go
修改syscolumns表中各列的列号,让它们的列号+1,然后把你加入的该列的列号设置为1,就可以了
alter table employee add test1 int null
go
sp_configure 'allow updates',1
RECONFIGURE WITH override
go
UPDATE syscolumns SET colid=colid+1
WHERE id=object_id('employee')
update syscolumns set colid=1
where id=object_id('employee') and name='test1'
go
sp_configure 'allow updates',0
RECONFIGURE WITH override
create table Tbb(aa nchar(10) NULL,a int NULL,b varchar(100) NULL)
insert into Tbb select 1,1,1
insert into Tbb select 2,2,2--增长一列ff,使之位于第一列:
CREATE TABLE dbo.Tmp_Tbb
(
ff nchar(10) NULL,
aa nchar(10) NULL,
a int NULL,
b varchar(100) NULL
) ON [PRIMARY]
GO
IF EXISTS(SELECT * FROM dbo.Tbb)
EXEC('INSERT INTO dbo.Tmp_Tbb (aa, a, b)
SELECT aa, a, b FROM dbo.Tbb WITH (HOLDLOCK TABLOCKX)')
GO
DROP TABLE dbo.Tbb
GO
EXECUTE sp_rename N'dbo.Tmp_Tbb', N'Tbb'
GO
--改栏位在syscolumns的colid值,其具体步骤如下: create table test_column(a char(1) null,b char(2) null,c char(1) null,d char(2)null)--栏位被删除前的顺序
select * from test_column
alter table test_column drop column c--栏位被删除后的顺序select * from test_column
alter table test_column add c varchar(1) null--栏位被添加后的顺序
select * from test_column--更改栏位的顺序:sp_configure 'allow updates','1'
go
reconfigure with override update syscolumns set colid=colid+1 where colid>3 and id=object_id('test_column')
update syscolumns set colid=3 where name='c' and id=object_id('test_column')
sp_configure 'allow updates','0'
go
reconfigure with override
go
现在我用sql语句添加了一列值,但是这个值本来显示的位置是13,但是现在它显示在20的这个位置上,我想在修改一下它的显示位置,要改回到13这个位置上去,怎么修改呢?