Why DROP COLUMN fails: the object 'DF__…' is dependent on column
You drop a column you no longer need, and SQL Server refuses:
Msg 5074, Level 16, State 1
The object 'DF__Customer__Status__239E4DCF' is dependent on column 'Status'.
Msg 4922, Level 16, State 9
ALTER TABLE DROP COLUMN Status failed because one or more objects access this column.
The column had a default value. In SQL Server a default is not a property of the column. It is a constraint, an object of its own, and a column cannot be dropped while something still depends on it. The same thing blocks changing the column's type:
ALTER TABLE ALTER COLUMN Amount failed because one or more objects access this column.
Why the name looks like that
If the default was declared without a name, like this:
ALTER TABLE dbo.Customer ADD Status varchar(10) NULL DEFAULT ('Active');
SQL Server names it for you: DF__, part of the table name, part of the column name, and a random
suffix. The suffix is different on every database the script ran against. The constraint on your
development copy is not called the same thing as the one in production, so you cannot write its
name into a deployment script and expect it to work everywhere.
Find it, then drop it
The name lives in sys.default_constraints:
SELECT dc.name
FROM sys.default_constraints AS dc
JOIN sys.columns AS c
ON c.object_id = dc.parent_object_id
AND c.column_id = dc.parent_column_id
WHERE dc.parent_object_id = OBJECT_ID(N'dbo.Customer')
AND c.name = N'Status';
To do it in one script that works on every copy of the database, look the name up and drop it with dynamic SQL, then drop the column:
DECLARE @name sysname, @sql nvarchar(max);
SELECT @name = dc.name
FROM sys.default_constraints AS dc
JOIN sys.columns AS c
ON c.object_id = dc.parent_object_id
AND c.column_id = dc.parent_column_id
WHERE dc.parent_object_id = OBJECT_ID(N'dbo.Customer')
AND c.name = N'Status';
IF @name IS NOT NULL
BEGIN
SET @sql = N'ALTER TABLE dbo.Customer DROP CONSTRAINT ' + QUOTENAME(@name);
EXEC (@sql);
END
ALTER TABLE dbo.Customer DROP COLUMN Status;
QUOTENAME matters. A generated name contains nothing odd, but a hand-written one can, and
building SQL from a name without quoting it is how scripts break on the one database where someone
was creative. Build the statement into a variable first: EXEC (...) accepts only strings and
variables, so calling QUOTENAME inside the brackets is a syntax error.
Changing the type instead
The same lookup works when you are changing the column rather than removing it. Drop the default, alter the column, and put the default back, this time with a name:
-- after dropping the old default as above
ALTER TABLE dbo.Customer ALTER COLUMN Amount bigint NULL;
ALTER TABLE dbo.Customer
ADD CONSTRAINT DF_Customer_Amount DEFAULT (0) FOR Amount;
If the old default no longer fits the new type, for example a text default on a column that is now numeric, this is also the moment to change it.
Stop it happening next time
Name your defaults when you create them:
ALTER TABLE dbo.Customer
ADD Status varchar(10) NULL CONSTRAINT DF_Customer_Status DEFAULT ('Active');
A named default has the same name on every database, so a script can drop it directly with no
lookup. Some teams enforce a convention like DF_<table>_<column>, and it pays off the first time
a column has to go.
Other things block a column drop in the same way: an index that includes the column, a foreign key
that uses it, a check constraint, a computed column that reads it, and a view or function created
WITH SCHEMABINDING. The error names the object each time, so read the first message rather than
the last.
Letting the script do it
Delete a column in WoodFireERD and the migration script removes its default first. It uses the constraint's name when the schema recorded one, and looks it up on the target database when it did not. The same applies when you change the type of a column that has a default: the default comes off for the change and goes back on afterwards.