Delete a SQL Table Column give Constraint dependency error

Question

I try to drop a column in SQL but I am getting this error:

The object 'DF__...'is dependent on column ...

I found a lot of solutions that need to drop the Constraint first, so I ran this and worked:

ALTER TABLE [dbo].[Configuration] DROP CONSTRAINT DF__SiteConfi__Na__2DFCAC08;
ALTER TABLE [dbo].[Configuration] DROP COLUMN NaFlag;

But I need this script to run on any server, so I don't want to mention the Constraint name as it may be different on any other servers. What is the best solution?

Does this answer your question? [How can I delete or alter a default constraint from table in SQL Server database for multiple database?](https://stackoverflow.com/questions/44325912/how-can-i-delete-or-alter-a-default-constraint-from-table-in-sql-server-database) — SMor, Apr 20 '22 at 17:29
The ONLY (not best) solution is to do exactly what you are trying to avoid. The key to doing this is to find that name and use it in your script - which requires dynamic sql. And let this be a lesson about bad habits. Give your constraints names and you can easily avoid this issue. — SMor, Apr 20 '22 at 17:30

score 1 · Accepted Answer · edited Apr 21 '22 at 04:02

You can use some dynamic SQL to drop the default. If it's an isolated script to just drop the column, then it's easier, something like:

DECLARE @sqlDF NVARCHAR(MAX);
SELECT @sqlDF = 'ALTER TABLE {$tableName} DROP CONSTRAINT ' + QUOTENAME(OBJECT_NAME([default_object_id])) + ';'
FROM sys.columns
WHERE [object_id] = OBJECT_ID('{$tableName}') AND [name] in ({$columns}) AND [default_object_id] <> 0;

IF @sqlDF IS NOT NULL
    EXEC(@sqlDF);

If you are working with a migrations tool, maybe you're gonna have to refactor this, so it doesn't try to redeclare the @sqlDF variable.

score 0 · Answer 2 · answered Apr 20 '22 at 17:41

Here's a query to get you started:

with q as
(
    select schema_name(t.schema_id) schema_name,
           t.name table_name,
           c.name column_name,
           d.name default_name
    from sys.tables t
    join sys.columns c
      on t.object_id = c.object_id
    join sys.default_constraints d
      on d.parent_object_id = t.object_id
     and d.parent_column_id = c.column_id
 )
select concat(
        'alter table ',
        quotename(schema_name),'.',quotename(table_name),
        ' drop constraint ', quotename(default_name) ) sql
from q

Delete a SQL Table Column give Constraint dependency error

2 Answers2