alter table XXX add column1.....

[cfanliang]

怎样使column1排在两个相邻的列之间啊？

[hellowork]

alter table XXX add column1..... 不能指定列的顺序,只能被追加成最后一列.修改列的顺序要修改syscolumns表的colorder列.

[gahade]

第1种方法:
create table table_test(id int,code varchar(10))
alter table table_test add name varchar(20)
update syscolumns
set colid = colid + 1
where name in ('code','name') and id = object_id('table_test')
GO
update syscolumns
set colid = 2
where name = 'name' and id = object_id('table_test')

第2种方法:
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
CREATE TABLE dbo.Tmp_table_test
(
id int NULL,
name varchar(20) NULL,
code varchar(10) NULL
) ON [PRIMARY]
GO
IF EXISTS(SELECT * FROM dbo.table_test)
EXEC('INSERT INTO dbo.Tmp_table_test (id, code)
SELECT id, code FROM dbo.table_test TABLOCKX')
GO
DROP TABLE dbo.table_test
GO
EXECUTE sp_rename N'dbo.Tmp_table_test', N'table_test', 'OBJECT'
GO
COMMIT