Column default value

Hi,
Anyone of you know how to change the default value for an existing column?
I have the script below but it might be a problem to run this script on
different server because the default constraint name might be different. Any
efficient way to solve this problem?
ALTER TABLE AcctSettings DROP CONSTRAINT DF_ShowOrganizationWidePopup
GO
ALTER TABLE [dbo].[AcctSettings] ADD
CONSTRAINT [DF_AcctSettings_ShowOrganization] DEFAULT( 0 ) FOR
[ShowOrganizationWidePopup]
GO
Thanks,
KennyKenny
See INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE view to get the name of
contraint
"Kenny" <keejh@.hotmail.com> wrote in message
news:%23DcH9j%23%23FHA.3676@.tk2msftngp13.phx.gbl...
> Hi,
> Anyone of you know how to change the default value for an existing column?
> I have the script below but it might be a problem to run this script on
> different server because the default constraint name might be different.
> Any efficient way to solve this problem?
> ALTER TABLE AcctSettings DROP CONSTRAINT DF_ShowOrganizationWidePopup
> GO
> ALTER TABLE [dbo].[AcctSettings] ADD
> CONSTRAINT [DF_AcctSettings_ShowOrganization] DEFAULT( 0 ) FOR
> [ShowOrganizationWidePopup]
> GO
> Thanks,
> Kenny
>|||Before dropping an existing constraint you should know its name. If the name
was created automatically (if the constraint was "created by clicking, rathe
r
by typing" :)), you need to identify its name first either by looking at the
objects in QA or by inspecting the INFORMATION_SCEMA views.
ML
http://milambda.blogspot.com/|||Unfortunately, default names are not in the information_schema tables (at le
ast not in
CONSTRAINT_COLUMN_USAGE). This is because in ANSI SQL, a default is a column
attribute, not a
constraint.
So one would have to look up the name in sysobjects and syscolumns.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"ML" <ML@.discussions.microsoft.com> wrote in message
news:02BFC401-F554-41C7-95FD-7B0D19071702@.microsoft.com...
> Before dropping an existing constraint you should know its name. If the na
me
> was created automatically (if the constraint was "created by clicking, rat
her
> by typing" :)), you need to identify its name first either by looking at t
he
> objects in QA or by inspecting the INFORMATION_SCEMA views.
>
> ML
> --
> http://milambda.blogspot.com/|||Tibor

> Unfortunately, default names are not in the information_schema tables (at
> least not in CONSTRAINT_COLUMN_USAGE). This is because in ANSI SQL, a
> default is a column attribute, not a constraint.
Yes, you are right, how could I forget about it.
Thanks
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23svTrw%23%23FHA.4088@.TK2MSFTNGP09.phx.gbl...
> Unfortunately, default names are not in the information_schema tables (at
> least not in CONSTRAINT_COLUMN_USAGE). This is because in ANSI SQL, a
> default is a column attribute, not a constraint.
> So one would have to look up the name in sysobjects and syscolumns.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "ML" <ML@.discussions.microsoft.com> wrote in message
> news:02BFC401-F554-41C7-95FD-7B0D19071702@.microsoft.com...
>