ALTER TABLE ... SET DEFAULT Syntax error

[josephwalter]

I'm trying to execute the following in Query Analyzer:

[tt]ALTER TABLE VR_MAIN_O4 ALTER COLUMN OPERDAYS DEFAULT '0'[/tt]

I get the error "Incorrect syntax near keywork 'DEFAULT'."

I tried changing DEFAULT to SET DEFAULT, and I tried taking the single quotes off the zero. Nothing but syntax errors... What am I overlooking?

 

[helper2]

Not sure if this will help, I took this from the SQL Books online. With a search on Alter Table.

Add a nullable column with default values
This example adds a nullable column with a DEFAULT definition, and uses WITH VALUES to provide values for each existing row in the table. If WITH VALUES is not used, each row has the value NULL in the new column.

ALTER TABLE MyTable
ADD AddDate smalldatetime NULL
CONSTRAINT AddDateDflt
DEFAULT getdate() WITH VALUES

 

[nigelrivett]

ALTER TABLE VR_MAIN_O4 add DEFAULT '0' for OPERDAYS

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[josephwalter]

Thank you, nigelrivett. That's what I was looking for.

Can you tell me the syntax to drop the default, too? Sql Server didn't like it when I changed the 'ADD' to 'DROP'...

 

[nigelrivett]

I think for the drop you have to find the constraint name and drop that.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[nigelrivett]

select o.* from sysconstraints c, sysobjects o where c.id = object_id('mytbl') and o.id = c.constid

will give the constraint name - see colid in sysconstraints for which col it applies to.

You can also use sp_help mytbl but that's not so easy if you want to produce a script to do it.

then

alter table mytbl drop constraint constraintname


======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.