CODEWITHSUNDEEP
sqlsql-server-2005
Maciej
Maciej

Reputation: 22338

How to remove a field from SQLServer2005 table

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:

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.

Upvotes: 5

Views: 7661

Answers (3)

SAI
SAI

Reputation: 1

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

Upvotes: 0

Brian R. Bondy
Brian R. Bondy

Reputation: 347216

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

Upvotes: 9

Lance McNearney
Lance McNearney

Reputation: 9490

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.

Upvotes: 4

Related Questions