如何基于列名删除默认约束并添加IF EXISTS()判断避免报错

现有一段SQL代码可基于列名删除指定表的默认约束，但当没有匹配的约束时，执行@Command会报错。需要添加判断逻辑，仅当存在要删除的约束时才执行命令，原代码如下：

declare @table_name nvarchar(256)
declare @Command nvarchar(max) = ''
set @table_name = N'ATableName'

select @Command = @Command + 'ALTER TABLE ' + @table_name + ' drop constraint ' + d.name + CHAR(10)+ CHAR(13)
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 in ('column1','column2','column3')

--print @Command
execute (@Command)

解决方法 #

方法1：检查生成的命令是否非空

直接在执行前判断@Command是否有内容，只有非空时才执行：

declare @table_name nvarchar(256)
declare @Command nvarchar(max) = ''
set @table_name = N'ATableName'

select @Command = @Command + 'ALTER TABLE ' + @table_name + ' drop constraint ' + d.name + CHAR(10)+ CHAR(13)
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 in ('column1','column2','column3')

-- 仅当命令非空时执行
IF @Command <> ''
BEGIN
    --print @Command
    execute (@Command)
END

方法2：先用EXISTS判断是否存在目标约束

先通过EXISTS确认存在符合条件的约束，再生成并执行命令：

declare @table_name nvarchar(256)
declare @Command nvarchar(max) = ''
set @table_name = N'ATableName'

-- 先判断是否存在要删除的约束
IF EXISTS(
    SELECT 1
    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 in ('column1','column2','column3')
)
BEGIN
    select @Command = @Command + 'ALTER TABLE ' + @table_name + ' drop constraint ' + d.name + CHAR(10)+ CHAR(13)
    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 in ('column1','column2','column3')

    --print @Command
    execute (@Command)
END

两种方法都能避免无约束时执行空命令报错：方法1更简洁，生成命令后直接检查内容；方法2先判断再生成命令，适合需要提前执行其他逻辑的场景。

内容的提问来源于stack exchange，提问作者lonelydev101