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