Updating Default Field Value

[TomBarrand]

How would I update a default value on a field without going into Enterprise Manager?

I am presuming there is some system stored procedure that can be called to do something like this.

Cheers

 

[dbtech]

The SQL command to use is ALTER TABLE ADD|DROP CONSTRAINT , once you have created a table with Enterprise Manager u need to know the default constraint name, u can make use of sp_helpconstraint SP to get the constraint name, Correct me if i'm wrong there is no way you can change the default value of a column with a SINGLE SQL statement, the only way to do is drop and recreate the default constraint

See the example below

/*Create table with default value for column test1 to 0 */
BEGIN TRANSACTION
go
CREATE TABLE dbo.Table4
(
test1 int NULL
) ON [PRIMARY]

go
ALTER TABLE dbo.Table4 ADD CONSTRAINT
DF_Table4_test1 DEFAULT 0 FOR test1
go

--Change the Default value of column test1 to 4
BEGIN TRANSACTION

ALTER TABLE dbo.Table4
DROP CONSTRAINT DF_Table4_test1
go
ALTER TABLE dbo.Table4 ADD CONSTRAINT
DF_Table4_test1 DEFAULT 1 FOR test1
dbtech

 

[dbtech]

oops there is a mistake in the code i posted above, i forgot to include the commit please ignore the above code and use this one.

/*Create table with default value for column test1 to 0 */
BEGIN TRANSACTION
go
CREATE TABLE dbo.Table4
(
test1 int NULL
) ON [PRIMARY]

go
ALTER TABLE dbo.Table4 ADD CONSTRAINT
DF_Table4_test1 DEFAULT 0 FOR test1

go
COMMIT

--Change the Default value of column test1 to 4
BEGIN TRANSACTION

ALTER TABLE dbo.Table4
DROP CONSTRAINT DF_Table4_test1
go
ALTER TABLE dbo.Table4 ADD CONSTRAINT
DF_Table4_test1 DEFAULT 1 FOR test1
go
COMMIT