ALTER TABLE DROP COLUMN failed because one or more objects access this column
You must remove the constraints
from the column before removing the column. The name you are referencing is a default constraint
.
e.g.
alter table CompanyTransactions drop constraint [df__CompanyTr__Creat__0cdae408];
alter table CompanyTransactions drop column [Created];
The @SqlZim's answer is correct but just to explain why this possibly have happened. I've had similar issue and this was caused by very innocent thing: adding default value to a column
ALTER TABLE MySchema.MyTable ADD
MyColumn int DEFAULT NULL;
But in the realm of MS SQL Server a default value on a colum is a CONSTRAINT. And like every constraint it has an identifier. And you cannot drop a column if it is used in a CONSTRAINT.
So what you can actually do avoid this kind of problems is always give your default constraints a explicit name, for example:
ALTER TABLE MySchema.MyTable ADD
MyColumn int NULL,
CONSTRAINT DF_MyTable_MyColumn DEFAULT NULL FOR MyColumn;
You'll still have to drop the constraint before dropping the column, but you will at least know its name up front.
As already written in answers you need to drop constraints (created automatically by sql) related to all columns that you are trying to delete.
Perform followings steps to do the needful.
- Get Name of all Constraints using sp_helpconstraint which is a system stored procedure utility - execute following
exec sp_helpconstraint '<your table name>'
- Once you get the name of the constraint then copy that constraint name and execute next statement i.e
alter table <your_table_name> drop constraint <constraint_name_that_you_copied_in_1>
(It'll be something like this only or similar format) - Once you delete the constraint then you can delete 1 or more columns by using conventional method i.e
Alter table <YourTableName> Drop column column1, column2
etc