如果表已经建好了,请问怎样用语句删除.增加或修改缺省值?

[jcrj]

如果表已经建好了,请问怎样用语句删除.增加或修改缺省值?

[zjcxc]

--删除默认值约束名
alter table 你的表 drop constraint 默认值约束名

--添加字段的默认值约束:
alter table 表 add constraint DF_表_字段名_112312
default ('默认值') for 字段名

[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 行）
--*/

[ropriest]

ALTER TABLE table

[ropriest]

ALTER TABLE table
{ [ALTER COLUMN column_name
{ new_data_type [ (precision[, scale] ) ]
[ 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 ]
| [ IDENTITY [(seed, increment ) [NOT FOR REPLICATION] ] ]
]
[ ROWGUIDCOL ]
[ <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) ]
[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])]
[NOT FOR REPLICATION]
| DEFAULT constant_expression
[FOR column]
| CHECK [NOT FOR REPLICATION]
(logical_expression)
}