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)

Answer 2

DECLARE @sql NVARCHAR(MAX) = N'ALTER TABLE SomeTable DROP CONSTRAINT DF_SmoeTable_ColName';

BEGIN TRY

    EXEC sp_executesql @sql;

END TRY

BEGIN CATCH

    IF ERROR_NUMBER() = 1785 -- Constraint does not exist

        PRINT 'Constraint DF_SmoeTable_ColName does not exist.';

    ELSE

        RAISERROR('An error occurred while dropping the constraint.', 16, 1);

END CATCH;

Try this code ,its going to work.

This code initially generates a SQL statement dynamically in order to remove the constraint. It proceeds to execute the statement by using a TRY...CATCH block. If the restriction is not present, an ERROR_NUMBER() of 1785 will be triggered, resulting in the display of a message. If a different mistake happens, it will result in an error being raised.

In this manner, you can try removing the restriction without concern for errors in case it is not present. If necessary, additional constraint names can be added to the @sql variable for adjustment.