如何通过SQL语句修改表结构，例如将某字段由Varchar(16)该为Varchar(32)

[ChenEric]

如何通过SQL语句修改表结构，
1、例如将某字段由Varchar(16)该为Varchar(32)
2、如何修改和删除含有约束的字段 如设置了字段的默认值：Default('')

脚本
Create Table t1
(
ID int Default(0),
Name Varchar(16) Default(16),
Memo Varchar(255) Default('')
)
通过脚本修改Name字段，删除Memo字段后改为
Create Table t1
(
ID int Default(0),
Name Varchar(32) Default(16)
)

[zjcxc]

--1.
alter table t1 drop column [name]
alter table t1 drop column [memo]
alter table t1 add memo varchar(32) default '16'

[progress99]

alter table t1
alter column Name Varchar(32) Default(16)

查看：
sp_columns t1

[progress99]

ALTER TABLE
通过更改、添加、除去列和约束，或者通过启用或禁用约束和触发器来更改表的定义。

语法
ALTER TABLE table
{ [ ALTER COLUMN column_name
{ new_data_type [ ( precision [ , scale ] ) ]
[ COLLATE < collation_name > ]
[ NULL | NOT NULL ]
| {ADD | DROP } ROWGUIDCOL }
]
| ADD
{ [ < column_definition > ]
| column_name AS computed_column_expression
} [ ,...n ]
| [ WITH CHECK | WITH NOCHECK ] ADD
{ < table_constraint > } [ ,...n ]
| DROP
{ [ CONSTRAINT ] constraint_name
| COLUMN column } [ ,...n ]
| { CHECK | NOCHECK } CONSTRAINT
{ ALL | constraint_name [ ,...n ] }
| { ENABLE | DISABLE } TRIGGER
{ ALL | trigger_name [ ,...n ] }
}

< column_definition > ::=
{ column_name data_type }
[ [ DEFAULT constant_expression ] [ WITH VALUES ]
| [ IDENTITY [ ( seed , increment ) [ NOT FOR REPLICATION ] ] ]
]
[ ROWGUIDCOL ]
[ COLLATE < collation_name > ]
[ < column_constraint > ] [ ...n ]

< column_constraint > ::=
[ CONSTRAINT constraint_name ]
{ [ NULL | NOT NULL ]
| [ { PRIMARY KEY | UNIQUE }
[ CLUSTERED | NONCLUSTERED ]
[ WITH FILLFACTOR = fillfactor ]
[ ON { filegroup | DEFAULT } ]
]
| [ [ FOREIGN KEY ]
REFERENCES ref_table [ ( ref_column ) ]
[ ON DELETE { CASCADE | NO ACTION } ]
[ ON UPDATE { CASCADE | NO ACTION } ]
[ NOT FOR REPLICATION ]
]
| CHECK [ NOT FOR REPLICATION ]
( logical_expression )
}

< table_constraint > ::=
[ CONSTRAINT constraint_name ]
{ [ { PRIMARY KEY | UNIQUE }
[ CLUSTERED | NONCLUSTERED ]
{ ( column [ ,...n ] ) }
[ WITH FILLFACTOR = fillfactor ]
[ ON { filegroup | DEFAULT } ]
]
| FOREIGN KEY
[ ( column [ ,...n ] ) ]
REFERENCES ref_table [ ( ref_column [ ,...n ] ) ]
[ ON DELETE { CASCADE | NO ACTION } ]
[ ON UPDATE { CASCADE | NO ACTION } ]
[ NOT FOR REPLICATION ]
| DEFAULT constant_expression
[ FOR column ] [ WITH VALUES ]
| CHECK [ NOT FOR REPLICATION ]
( search_conditions )
}



示例
A. 更改表以添加新列
下例添加一个允许空值的列，而且没有通过 DEFAULT 定义提供值。各行的新列中的值将为 NULL。

CREATE TABLE doc_exa ( column_a INT)
GO
ALTER TABLE doc_exa ADD column_b VARCHAR(20) NULL
GO
EXEC sp_help doc_exa
GO
DROP TABLE doc_exa
GO

B. 更改表以除去列
下例修改表以删除一列。

CREATE TABLE doc_exb ( column_a INT, column_b VARCHAR(20) NULL)
GO
ALTER TABLE doc_exb DROP COLUMN column_b
GO
EXEC sp_help doc_exb
GO
DROP TABLE doc_exb
GO

C. 更改表以添加具有约束的列
下例向表中添加具有 UNIQUE 约束的新列。

CREATE TABLE doc_exc ( column_a INT)
GO
ALTER TABLE doc_exc ADD column_b VARCHAR(20) NULL
CONSTRAINT exb_unique UNIQUE
GO
EXEC sp_help doc_exc
GO
DROP TABLE doc_exc
GO

D. 更改表以添加未验证的约束
下例向表中的现有列上添加约束。该列中存在一个违反约束的值；因此，利用 WITH NOCHECK 来防止对现有行验证约束，从而允许该约束的添加。

CREATE TABLE doc_exd ( column_a INT)
GO
INSERT INTO doc_exd VALUES (-1)
GO
ALTER TABLE doc_exd WITH NOCHECK
ADD CONSTRAINT exd_check CHECK (column_a > 1)
GO
EXEC sp_help doc_exd
GO
DROP TABLE doc_exd
GO

E. 更改表以添加多个带有约束的列
下例向表中添加多个带有约束的新列。第一个新列具有 IDENTITY 属性；表中每一行的标识列都将具有递增的新值。

CREATE TABLE doc_exe ( column_a INT CONSTRAINT column_a_un UNIQUE)
GO
ALTER TABLE doc_exe ADD

/* Add a PRIMARY KEY identity column. */
column_b INT IDENTITY
CONSTRAINT column_b_pk PRIMARY KEY,

/* Add a column referencing another column in the same table. */
column_c INT NULL
CONSTRAINT column_c_fk
REFERENCES doc_exe(column_a),

/* Add a column with a constraint to enforce that */
/* nonnull data is in a valid phone number format. */
column_d VARCHAR(16) NULL
CONSTRAINT column_d_chk
CHECK
(column_d IS NULL OR
column_d LIKE "[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]" OR
column_d LIKE
"([0-9][0-9][0-9]) [0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]"),

/* Add a nonnull column with a default. */
column_e DECIMAL(3,3)
CONSTRAINT column_e_default
DEFAULT .081
GO
EXEC sp_help doc_exe
GO
DROP TABLE doc_exe
GO

F. 添加具有默认值的可为空的列
下例添加可为空的、具有 DEFAULT 定义的列，并使用 WITH VALUES 为表中的各现有行提供值。如果没有使用 WITH VALUES，那么每一行的新列中都将具有 NULL 值。

ALTER TABLE MyTable
ADD AddDate smalldatetime NULL
CONSTRAINT AddDateDflt
DEFAULT getdate() WITH VALUES

G. 禁用并重新启用一个约束
下例禁用用于限制可接受的薪水数据的约束。WITH NOCHECK CONSTRAINT 与 ALTER TABLE 一起使用，以禁用该约束并使正常情况下会引起约束违规的插入操作得以执行。WITH CHECK CONSTRAINT 重新启用该约束。

CREATE TABLE cnst_example
(id INT NOT NULL,
name VARCHAR(10) NOT NULL,
salary MONEY NOT NULL
CONSTRAINT salary_cap CHECK (salary < 100000)
)

-- Valid inserts
INSERT INTO cnst_example VALUES (1,"Joe Brown",65000)
INSERT INTO cnst_example VALUES (2,"Mary Smith",75000)

-- This insert violates the constraint.
INSERT INTO cnst_example VALUES (3,"Pat Jones",105000)

-- Disable the constraint and try again.
ALTER TABLE cnst_example NOCHECK CONSTRAINT salary_cap
INSERT INTO cnst_example VALUES (3,"Pat Jones",105000)

-- Reenable the constraint and try another insert, will fail.
ALTER TABLE cnst_example CHECK CONSTRAINT salary_cap
INSERT INTO cnst_example VALUES (4,"Eric James",110000)

H. 禁用并重新启用触发器
下例使用 ALTER TABLE 的 DISABLE TRIGGER 选项来禁用触发器，以使正常情况下会违反触发器条件的插入操作得以执行。然后下例使用 ENABLE TRIGGER 重新启用触发器。

CREATE TABLE trig_example
(id INT,
name VARCHAR(10),
salary MONEY)
go
-- Create the trigger.
CREATE TRIGGER trig1 ON trig_example FOR INSERT
as
IF (SELECT COUNT(*) FROM INSERTED
WHERE salary > 100000) > 0
BEGIN
print "TRIG1 Error: you attempted to insert a salary > $100,000"
ROLLBACK TRANSACTION
END
GO
-- Attempt an insert that violates the trigger.
INSERT INTO trig_example VALUES (1,"Pat Smith",100001)
GO
-- Disable the trigger.
ALTER TABLE trig_example DISABLE TRIGGER trig1
GO
-- Attempt an insert that would normally violate the trigger
INSERT INTO trig_example VALUES (2,"Chuck Jones",100001)
GO
-- Re-enable the trigger.
ALTER TABLE trig_example ENABLE TRIGGER trig1
GO
-- Attempt an insert that violates the trigger.
INSERT INTO trig_example VALUES (3,"Mary Booth",100001)
GO

[shuiniu]

按楼主的要求，这样就ok啦！
alter table t1 drop column memo
alter table t1 alter column name varchar(32)---这样修改后name字段上的默认值还在！

[zjcxc]

--上面我写的错了,这样改:

declare @s varchar(8000)
set @s=''
select @s=@s+'
alter table t1 drop constraint ['+name+']'
from sysobjects a
where parent_obj=object_id('t1')
exec(@s)
alter table t1 drop column [name]
alter table t1 alter column [memo] varchar(32)

alter table t1 add constraint
df__t1__id__531856c7 default (0) for id

alter table t1 add constraint
df__t1__memo__55009f39 default (32) for memo

[zjcxc]

--测试

--测试表
Create Table t1
(
ID int Default(0),
Name Varchar(16) Default(16),
Memo Varchar(255) Default('')
)
go

--处理
declare @s varchar(8000)
set @s=''
select @s=@s+'
alter table t1 drop constraint ['+name+']'
from sysobjects a
where parent_obj=object_id('t1')
exec(@s)
alter table t1 drop column [name]
alter table t1 alter column [memo] varchar(32)

alter table t1 add constraint
df__t1__id__531856c7 default (0) for id

alter table t1 add constraint
df__t1__memo__55009f39 default (32) for memo
go

--插入数据测试
insert t1 values(default,default)
select * from t1
go

--删除测试
drop table t1

/*--测试结果
ID Memo
----------- --------------------------------
0 32

（所影响的行数为 1 行）
--*/

[zjcxc]

--好一点的方法是这样写:

--测试表
Create Table t1(
ID int Default(0),
Name Varchar(16) Default(16),
Memo Varchar(255) Default('')
)
go

--要求,删除name字段,将memo字段改为: Memo Varchar(32) Default(32)

--处理方法

----a.先删除要处理字段的默认值约束
declare @s varchar(8000)
set @s=''
select @s=@s+'
alter table ['+b.name+'] drop constraint ['+d.name+']'
from syscolumns a
join sysobjects b on a.id=b.id
join syscomments c on a.cdefault=c.id
join sysobjects d on c.id=d.id
where b.name='t1'
and (a.name='name' or a.name='memo')
exec(@s)

----b.再删除字段 name
alter table t1 drop column [name]

----c.修改字段 memo
alter table t1 alter column [memo] varchar(32)

----d.为字段 memo 添加默认值约束
set @s='alter table t1 add constraint
[df__t1__memo__'+cast(newid() as varchar(36))
+'] default (32) for memo'
exec(@s)
go

--插入数据测试
insert t1 values(default,default)
select * from t1
go

--删除测试
drop table t1

/*--测试结果
ID Memo
----------- --------------------------------
0 32

（所影响的行数为 1 行）
--*/

[zjcxc]

--好一点的方法是这样写:

--测试表
Create Table t1(
ID int Default(0),
Name Varchar(16) Default(16),
Memo Varchar(255) Default('')
)
go

--要求,删除name字段,将memo字段改为: Memo Varchar(32) Default(32)

--处理方法

----a.先删除要处理字段的默认值约束
declare @s varchar(8000)
set @s=''
select @s=@s+'
alter table ['+b.name+'] drop constraint ['+d.name+']'
from syscolumns a
join sysobjects b on a.id=b.id
join syscomments c on a.cdefault=c.id
join sysobjects d on c.id=d.id
where b.name='t1'
and (a.name='name' or a.name='memo')
exec(@s)

----b.再删除字段 name
alter table t1 drop column [name]

----c.修改字段 memo
alter table t1 alter column [memo] varchar(32)

----d.为字段 memo 添加默认值约束
set @s='alter table t1 add constraint
[df__t1__memo__'+cast(newid() as varchar(36))
+'] default (32) for memo'
exec(@s)
go

--插入数据测试
insert t1 values(default,default)
select * from t1
go

--删除测试
drop table t1

/*--测试结果
ID Memo
----------- --------------------------------
0 32

（所影响的行数为 1 行）
--*/

[LoveSQL]

--delete constraint
declare @constraint varchar(200),@str varchar(3000)
select @constraint=a.name from sysobjects a where a.parent_obj=object_id('t1') and name like '%memo%'
set @str='alter table t1 drop constraint ' + @constraint
exec(@str)
go
--alter and drop columns
alter table t1 alter column name varchar(32)
alter table t1 drop column memo

[newdongkui]

佩服 zjcxc(: 邹建 :) 大帅哥的热情和精力，
删除字段，也不用先删除上面的约束，缺省值啊啥的把。
直接删呗。
楼主就十删字段，

[zjcxc]

to: newdongkui(老乌鸦)

不删除约束,你试试能否直接删除字段,反正我试过就不行.

[leeboyan]

2.更改列的属性

例：表table2中的列id为int型，现改为char型
alter table table2 alter column id char

3.增加列

A.例：在表table2中增加name列为char型
alter table table2 add name char(8)
B.例：在表table2中增加nob列为int型且设自增量为1,且不空
alter table table2 add nob int identity(1,1) not null

4.删除列

例：删除table2中的id列
alter table table2 drop column id