删除字段
ALTER TABLE table_NAME DROP COLUMN column_NAME
修改字段类型
ALTER TABLE table_name ALTER COLUMN column_name new_data_type
改名
sp_rename
更改当前数据库中用户创建对象(如表、列或用户定义数据类型)的名称。语法
sp_rename [ @objname = ] 'object_name' ,
[ @newname = ] 'new_name'
[ , [ @objtype = ] 'object_type' ]
ALTER TABLE table_NAME DROP COLUMN column_NAME
修改字段类型
ALTER TABLE table_name ALTER COLUMN column_name new_data_type
改名
sp_rename
更改当前数据库中用户创建对象(如表、列或用户定义数据类型)的名称。语法
sp_rename [ @objname = ] 'object_name' ,
[ @newname = ] 'new_name'
[ , [ @objtype = ] 'object_type' ]
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='Tabel1'
and a.name in('a','b','c')
exec(@s)--再修改字段类型
alter table table1 alter column a money
alter table table1 alter column b money
alter table table1 alter column c money--为字段加回默认值
set @s='alter table table1 add constraint
[df__table1__a__'+cast(newid() as varchar(36))
+'] default (0) for a'
exec(@s)
set @s='alter table table1 add constraint
[df__table1__b__'+cast(newid() as varchar(36))
+'] default (0) for b'
exec(@s)
set @s='alter table table1 add constraint
[df__table1__c__'+cast(newid() as varchar(36))
+'] default (0) for c'
exec(@s)
--或者这样处理:--原字段改名
exec sp_rename 'table1.a','a_bak'
exec sp_rename 'table1.b','b_bak'
exec sp_rename 'table1.c','c_bak'
go--添加新字段
alter table table1 add a money default 0
alter table table1 add b money default 0
alter table table1 add c money default 0
go--旧列数据复制到新列
update table1 set a=a_bak,b=b_bak,c=c_bak
go--删除旧列字段
alter table table1 drop column a_bak
alter table table1 drop column b_bak
alter table table1 drop column c_bak
alter table table1 drop column a_bak
alter table table1 drop column b_bak
alter table table1 drop column c_bak
这个执行失败,原因是它有默认值,不可以删除,不过这样也可以用了,谢谢。结贴。