By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,419 Members | 1,609 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,419 IT Pros & Developers. It's quick & easy.

changing the default value for a column

P: n/a
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
Jul 23 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
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
--

Jul 23 '05 #2

P: n/a
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
Jul 23 '05 #3

P: n/a
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
--

Jul 23 '05 #4

P: n/a
Hi,

something like this could meet your requirements:

declare @myStatement nvarchar(4000)

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

EXEC sp_executesql @myStatement
go

Karsten

"Johan Vervloet" <jo****@chiro.be> 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


Jul 23 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.