SQL Server error 5074: 'The object is dependent on column' and 4922 'ALTER TABLE DROP COLUMN failed'

The object 'DF__Tickets__Status__160F4887' is dependent on column 'Status'. (error 5074), followed by error 4922, means an object still depends on the column you are dropping, here a default constraint. Drop the object named in the message first, then drop the column.

A name that starts with DF__ and ends in a hex number is a default constraint that SQL Server named for you. That name changes every time the table is created, so scripts should look it up at run time instead of hard-coding it.

The error

ALTER TABLE dbo.Tickets DROP COLUMN Status;

Msg 5074, Level 16, State 1, Server MACHINE\LOCALDB#EAD42A01, Line 2
The object 'DF__Tickets__Status__160F4887' is dependent on column 'Status'.
Msg 4922, Level 16, State 9, Server MACHINE\LOCALDB#EAD42A01, Line 2
ALTER TABLE DROP COLUMN Status failed because one or more objects access this column.

Why it happens

The table was created with Status nvarchar(20) NOT NULL DEFAULT (N'Open'). That inline DEFAULT is a separate object, a default constraint bound to the column, and SQL Server will not drop a column while an object depends on it. Error 5074 names the dependent object; error 4922 is the ALTER TABLE itself failing as a result.

Because the constraint was not named in the CREATE TABLE, SQL Server generated the name. The table was created twice in the test, and the two runs produced DF__Tickets__Status__160F4887 and DF__Tickets__Status__29221CFB. A migration that hard-codes the name from a developer's machine is likely to fail elsewhere, because the generated name there will differ.

The fix

DECLARE @df sysname = (
    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.Tickets') AND c.name = N'Status');
DECLARE @sql nvarchar(400) = N'ALTER TABLE dbo.Tickets DROP CONSTRAINT ' + QUOTENAME(@df);
EXEC sys.sp_executesql @sql;
ALTER TABLE dbo.Tickets DROP COLUMN Status;
SELECT name FROM sys.columns WHERE object_id = OBJECT_ID(N'dbo.Tickets') ORDER BY column_id;

The last query listed only Id and Title: the constraint and then the column were gone. The lookup finds the default constraint on that column whatever it is called, so the script works on any copy of the database. The first attempt at this script used EXEC (N'...' + QUOTENAME(@df)) and failed with "Incorrect syntax near 'QUOTENAME'"; building the string in a variable first and running it with sp_executesql worked. For new tables, name the constraint yourself, CONSTRAINT DF_Tickets_Status DEFAULT (N'Open'), and a plain ALTER TABLE dbo.Tickets DROP CONSTRAINT DF_Tickets_Status will do.

How it was reproduced

One script run with sqlcmd -S "(localdb)\MSSQLLocalDB" -d fix8_lab -I -i e5074.sql -W on SQL Server 2022 LocalDB 16.0.1210.3 and sqlcmd 16.0.1000.6, in a throwaway database fix8_lab created for the test and dropped afterwards. A tickets table with an unnamed default on its status column. The server's computer name in the messages is replaced by MACHINE; nothing else changed.

Frequently asked

Why does ALTER TABLE DROP COLUMN fail with 'one or more objects access this column'?

Error 4922 follows error 5074, which names the object that depends on the column. Usually it is a default constraint. Drop that constraint first, then drop the column.

What is a DF__ constraint in SQL Server?

A name like DF__Tickets__Status__160F4887 is a default constraint whose name SQL Server generated because the CREATE TABLE did not name it. The name changes every time the table is created.

How do I drop a default constraint without knowing its name?

Look it up in sys.default_constraints joined to sys.columns for the table and column, build the ALTER TABLE ... DROP CONSTRAINT statement with QUOTENAME, and run it with sp_executesql.

More decoded errors in the Fixes category.