创建一个简单的数据表,然后对此表进行操作
CREATE TABLE Test ( col_a INT)
1.增加一个新的列,列名为col_b
ALTER TABLE test ADD col_b VARCHAR(20) NULL
2.修改一个列值的长度(col_b)
Alter TABLE test Alter column col_b varchar(100)
3.删除一个列
ALTER TABLE Test DROP COLUMN col_b
4.对某个列增加一个默认值(为col_b列增加一个默认值'')
ALTER TABLE test ADD constraint test_col_b default('') for col_b
5.修改某个列的默认值(先删除默认约束,然后进行添加)
ALTER TABLE test DROP constraint test_col_b
ALTER TABLE test ADD constraint test_col_b default('123') for col_b
对已经存在的默认值列,自动来实现对默认值的修改(可做成存储过程)
declare @tablename varchar(30)
declare @fieldname varchar(50)
Declare @defvalue varchar(50)
declare @sql varchar(300)
set @tablename='test'
set @fieldname='cname'
Set @defValue='T002'
set @sql=''
select @sql=@sql+'
alter table ['+a.name+'] drop constraint ['+d.name+'] Alter Table [' + a.name + '] Add constraint [' + d.name + '] default(''' + @defValue + ''') for ' + @fieldname
from sysobjects a
join syscolumns b on a.id=b.id
join syscomments c on b.cdefault=c.id
join sysobjects d on c.id=d.id
where a.name=@tablename and b.name=@fieldname
exec(@sql)
6.删除某个列的默认值
ALTER TABLE test DROP constraint test_col_b