How to remove a field from SQLServer2005 table

Question

I tried this:

ALTER TABLE My.Table DROP MyField

and got this error:

-MyField is not a constraint.

-Could not drop constraint. See previous errors.

There is just one row of data in the table and the field was just added.

EDIT: Just to follow up, the sql was missing COLUMN indeed. Now I get even more seriously looking errors though:

• The object 'some_object__somenumbers' is dependent on column 'MyField'
• ALTER TABLE DROP COLUMN MyField failed because one or more objects access this column.

EDIT:

ALTER TABLE TableName DROP Constraint ConstraintName

worked, after that I was able to use the previous code to remove the column. Credit goes to both of you, thanks.

Answer 1

I think you are just missing the COLUMN keyword:

ALTER TABLE TableName DROP COLUMN ColumnName

You will also need to make sure that any constraint that is depending on ColumnName is dropped first.

You can do this by:

ALTER TABLE TableName DROP ConstraintName

For each constraint that you have.

If you have indexes based on the column, you will also need to drop those indexes first.

DROP INDEX TableName.IndexName

Answer 2

Brian solved your original problem - for your new problem (The object 'some_object__somenumbers' is dependent on column 'MyField') it means you have a dependancy issue. Something like an index, foreign key reference, default value, etc. To drop the constraint use:

ALTER TABLE TableName DROP ConstraintName

Also - you'll need to drop all the constraints dependant on that column before it'll let you drop the column itself.

Answer 3

ALTER TABLE TABLE_NAME ADD COLUMN SR_NO INTEGER(10)NOT NULL;