How to drop SQL default constraint without knowing its name?

user_26305 0
Viewed 195775

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.

tsql sql-server sql
14 Answers
declare @table_name nvarchar(100)
declare @col_name nvarchar(100)
declare @constraint nvarchar(100)
set @table_name = N'TableName'
set @col_name = N'ColumnName'

IF EXISTS (select       c.*
    from        sys.columns c 
    inner join  sys.tables t on t.object_id = c.object_id
    where       t.name = @table_name
    and         c.name = @col_name) 
BEGIN

select @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

    IF LEN(ISNULL(@constraint, '')) <> 0
    BEGIN
        DECLARE @sqlcmd VARCHAR(MAX)
        SET @sqlcmd = 'ALTER TABLE ' + QUOTENAME(@table_name) + ' DROP CONSTRAINT' + 
        QUOTENAME(@constraint);
        EXEC (@sqlcmd);

    END

END
GO
user_28325770
Related