T-SQL to change a default value.

[Chris Scheers]

I'm a newbie to MSSQL7, and am a bit mystified as to how to proceed.

I need to write a T-SQL script to update a database without disturbing
existing data. Part of the update includes clearing the NULL flag and
setting a default value.

I have code to clear the NULL flag. I can also set a default value, but
only if one doesn't already exist.

To change a default, the documentation for ALTER TABLE says that the
existing DEFAULT must be dropped, but it doesn't say how.

It appears that the default value is actually a constraint. Quite
often, the constraint is named DF_<table>_<column>, but not
necessarilly. If I can find the constraint name, it appears that I can
drop it and then add a new default value.

How can I find the name of the constraint that implements the DEFAULT
value?

How can I find out if the column currently has a DEFAULT value?

Am I going at this wrong?

Thanx!

-----------------------------------------------------------------------
Chris Scheers, Applied Synergy, Inc.

Voice: 817-237-3360 Internet: ch...@applied-synergy.com
Fax: 817-237-3074

[Dan Guzman]

To list the current constraints on a table:

EXEC sp_help TableName

Hope this helps.

"Chris Scheers" <ch...@applied-synergy.com> wrote in message
news:96ACF408D83E4F38.25603A78...@lp.airnews.net...

[Chris Scheers]

Dan Guzman wrote:
>
> To list the current constraints on a table:
>
> EXEC sp_help TableName
>
> Hope this helps.

Not really. I need to know how to do this from a script. Any
suggestions?

Is there an FAQ for this type of question?

[Dick Christoph]

Hi Chris

To Drop a constriant (Default value) you would say:

ALTER TABLE TestNames Drop CONSTRAINT [DF_TestNames_Value]

To Create a Default value you would say:

ALTER TABLE TestNames WITH NOCHECK ADD
CONSTRAINT [DF_TestNames_Value] DEFAULT (0) FOR [Value]
GO

To find out iof a constraint exists, look at the code in stored procedure
sp_HelpConstraint in the Master Database. Use the Ent. Mgr to go to that SP
and check its properties.

That procedure returns the constratint name in the second result set and by
copying and editing this code you can probably determine which system tables
to query to determine if a constraint exists.

Be very careful of course, not to actually alter the Text of
sp_HelpConstraint, or that function may stop working on your SQL Server.

HTH

-Dick Christoph