ALTER TABLE DROP COLUMN failed because one or more objects access this column

Question

I am trying to do this   ALTER TABLE CompanyTransactions DROP COLUMN Created   But I get this      Msg 5074  Level 16  State 1  Line 2   The object  DF  CompanyTr  Creat  0CDAE408  is dependent on column  Created     Msg 4922  Level 16  State 9  Line 2   ALTER TABLE DROP COLUMN Created failed because one or more objects access this column    This is a code first table   Somehow the migrations have become all messed up and I am trying to manually roll back some changed   I have no idea what this is   DF  CompanyTr  Creat  0CDAE408

User · Accepted Answer

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

User · Answer

You need to do a few things    You first need to check if the constrain exits in the information schema then you need to query by joining the sys default constraints and sys columns if the columns and default constraints have the same object ids When you join in step 2  you would get the constraint name from default constraints  You drop that constraint  Here is an example of one such drops I did    -- 1  Remove constraint and drop column IF EXISTS SELECT             FROM INFORMATION SCHEMA COLUMNS           WHERE TABLE NAME   N TABLE NAME              AND COLUMN NAME   N LOWER LIMIT      BEGIN     DECLARE  sql NVARCHAR MAX      WHILE 1 1         BEGIN             SELECT TOP 1  sql   N alter table  TABLE NAME  drop constraint    dc name N                FROM sys default constraints dc             JOIN sys columns c             ON c default object id   dc object id             WHERE dc parent object id   OBJECT ID   TABLE NAME    AND c name   N LOWER LIMIT              IF   ROWCOUNT   0                 BEGIN                     PRINT  DELETED Constraint on column LOWER LIMIT                      BREAK                 END         EXEC   sql      END      ALTER TABLE TABLE NAME DROP COLUMN LOWER LIMIT      PRINT  DELETED column LOWER LIMIT     END ELSE    PRINT  Column LOWER LIMIT does not exist  GO

User · Answer

I had the same problem and this was the script that worked for me with a table with a two part name separated by a period  quot   quot   USE  DATABASENAME  GO ALTER TABLE  TableNamePart1   TableNamePart2  DROP CONSTRAINT  DF   TableNamePart1D  ColumnName  5AEE82B9  GO ALTER TABLE  TableNamePart1    TableNamePart1  DROP COLUMN  ColumnName  GO

User · Answer

When you alter column datatype you need to change constraint key for every database    alter table CompanyTransactions drop constraint  df  CompanyTr  Creat  0cdae408

User · Answer

In addition to accepted answer  if you re using Entity Migrations for updating database  you should add this line at the beggining of the Up   function in your migration file  Sql  quot alter table dbo CompanyTransactions drop constraint  df  CompanyTr  Creat  0cdae408   quot     You can find the constraint name in the error at nuget packet manager console which starts with FK dbo

User · Answer

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

User · Answer

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   lt your table name gt   Once you get the name of the constraint then copy that constraint name and execute next statement i e alter table  lt your table name gt   drop constraint  lt constraint name that you copied in 1 gt   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  lt YourTableName gt  Drop column column1  column2 etc

[c#] ALTER TABLE DROP COLUMN failed because one or more objects access this column

Examples related to c#

Examples related to sql

Examples related to sql-server

Examples related to code-first