changing the default value for a column

[Johan Vervloet]

Does anybody know how I can change the default value for a column?
I was trying to remove the default value in order to add the new one
afterwards. This is what I tried:

alter table /table-name/ drop default for /column-name/
alter table /table-name/ alter column /column-name/
(/new-decl-without-default/)

It did not work. I cannot find a solution in the documentation. Maybe
you can help me out?

Thank you,
Johan

[David Portas]

ALTER TABLE table_name
DROP CONSTRAINT name_of_default _constraint

ALTER TABLE table_name
ADD CONSTRAINT name_of_default _constraint DEFAULT 123 FOR column_name
--
David Portas
SQL Server MVP
--

[Johan Vervloet]

David Portas schreef:

ALTER TABLE table_name
DROP CONSTRAINT name_of_default _constraint

Thank you for the quick reply. But how do I know the
name_of_default _constraint? I created the default value like this:

alter table /table_name/ add default /default_value/ for /column_name/

Johan

[David Portas]

It's a good idea to give constraints meaningful names when you create
them, otherwise the server assigns them an obscure unique identifier
name. Use a consistent naming convention and then you'll know the
constraint names for tables and columns.

You can find the name of an existing default using the Object Browser
in Query Analyzer or in the output of sp_help 'table_name'
--
David Portas
SQL Server MVP
--

[Karsten Stelling]

Hi,

something like this could meet your requirements:

declare @myStatement nvarchar(4000)

select @myStatement=(' ALTER TABLE RP_CUSTOM_ATTRI BUTE DROP CONSTRAINT ' +
(select so.name
from sysobjects so, syscolumns sc
where sc.name = 'YOUR_COLUMN_NA ME' and sc.cdefault = so.id))

EXEC sp_executesql @myStatement
go

Karsten

"Johan Vervloet" <jo****@chiro.b e> schrieb im Newsbeitrag
news:41******** *************** @news.xs4all.nl ...

David Portas schreef:

ALTER TABLE table_name
DROP CONSTRAINT name_of_default _constraint

Thank you for the quick reply. But how do I know the
name_of_default _constraint? I created the default value like this:

alter table /table_name/ add default /default_value/ for /column_name/

Johan