How to drop SQL default constraint without knowing its name?

Question

In Microsoft SQL Server, I know the query to check if a default constraint exists for a column and drop a default constraint is:

IF EXISTS(SELECT * FROM sysconstraints

  WHERE id=OBJECT_ID('SomeTable')

  AND COL_NAME(id,colid)='ColName'

  AND OBJECTPROPERTY(constid, 'IsDefaultCnst')=1)    

ALTER TABLE SomeTable DROP CONSTRAINT DF_SomeTable_ColName

But due to typo in previous versions of the database, the name of the constraint could be DF_SomeTable_ColName or DF_SmoeTable_ColName.

How can I delete the default constraint without any SQL errors? Default constraint names don't show up in INFORMATION_SCHEMA table, which makes things a bit trickier.

So, something like 'delete the default constraint in this table/column', or 'delete DF_SmoeTable_ColName', but don't give any errors if it can't find it.

Answer 1

You can generate the command to drop the constraint and dynamically execute it, by using the code as follows:

declare @schema_name nvarchar(256)

declare @table_name nvarchar(256)

declare @col_name nvarchar(256)

declare @Command  nvarchar(1000)

set @schema_name = N'MySchema'

set @table_name = N'Department'

set @col_name = N'ModifiedDate'

select @Command = 'ALTER TABLE ' + @schema_name + '.[' + @table_name + '] DROP CONSTRAINT ' + d.name

 from sys.tables t

  join sys.default_constraints d on d.parent_object_id = t.object_id

  join sys.columns c on c.object_id = t.object_id and c.column_id = d.parent_column_id

 where t.name = @table_name

  and t.schema_id = schema_id(@schema_name)

  and c.name = @col_name

--print @Command

execute (@Command)