Robo Robo - 7 months ago 32
SQL Question

How to drop SQL default constraint without knowing its name?

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

Expanding on Mitch Wheat's code, the following script will generate the command to drop the constraint and dynamically execute it.

declare @table_name nvarchar(256)
declare @col_name nvarchar(256)
declare @Command  nvarchar(1000)

set @table_name = N'Department'
set @col_name = N'ModifiedDate'

select @Command = 'ALTER TABLE ' + @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 c.name = @col_name

--print @Command

execute (@Command)