Please help with ALTER TABLE in MS-SQL

[netbros]

I can't seem to find the problem with this statement, yet it just won't run:

ALTER TABLE Table1 ALTER COLUMN Field1 smallint NOT NULL DEFAULT 0;

I'm trying to set the default value of an existing column, but it gives an error. I also tried:

ALTER TABLE Table1 ALTER COLUMN Field1 smallint DEFAULT 0 NOT NULL;

I know it's possible, as it can be done through Enterprise Manager, but SQL doesn't allow it.

Any ideas?

Regards

-- John

[eriksmoen]

try this:

ALTER TABLE Table1 MODIFY Field1 smallint NOT NULL DEFAULT 0;

hope that helps...

[netbros]

This seems to work for me:

ALTER TABLE Table1 ADD CONSTRAINT DF_Table1_Field1 DEFAULT '0' FOR Field1;

If the column already has a default, you need to drop it first. Only thing I can't figure out is where I find the name of the constraint in order to do so.

Regards

-- John