在Microsoft SQL Server中,我知道检查一个列是否存在默认约束并删除默认约束的查询是:

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

但是由于以前版本的数据库的拼写错误,约束的名称可能是DF_SomeTable_ColName或DF_SmoeTable_ColName。

我如何删除默认约束没有任何SQL错误?默认的约束名称不会显示在INFORMATION_SCHEMA表中,这使得事情变得有点棘手。

因此,类似于'删除这个表/列中的默认约束',或'删除DF_SmoeTable_ColName',但如果它找不到它,不要给出任何错误。


当前回答

扩展Mitch Wheat的代码,下面的脚本将生成删除约束并动态执行它的命令。

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)

其他回答

Rob Farley的博客文章可能会有所帮助:

有两种方法可以在不知道默认约束名称的情况下查找/删除它

喜欢的东西:

 declare @table_name nvarchar(256)
 declare @col_name nvarchar(256)
 set @table_name = N'Department'
 set @col_name = N'ModifiedDate'

 select t.name, c.name, d.name, d.definition
 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
declare @ery nvarchar(max)
declare @tab nvarchar(max) = 'myTable'
declare @qu nvarchar(max) = 'alter table '+@tab+' drop constraint '

select @ery = (select bj.name from sys.tables as tb 
inner join sys.objects as bj 
on tb.object_id = bj.parent_object_id
where tb.name = @tab and bj.type = 'PK')

exec(@qu+@ery)

来看看。

适用于创建了多个默认约束或检查约束的列:

修改https://stackoverflow.com/a/16359095/206730脚本

注意:此脚本用于sys.check_constraints

declare @table_name nvarchar(128)
declare @column_name nvarchar(128)
declare @constraint_name nvarchar(128)
declare @constraint_definition nvarchar(512)

declare @df_name nvarchar(128)
declare @cmd nvarchar(128) 

PRINT 'DROP CONSTRAINT [Roles2016.UsersCRM].Estado'

declare constraints cursor for 
 select t.name TableName, c.name ColumnName, d.name ConstraintName, d.definition ConstraintDefinition
 from sys.tables t   
 join sys.check_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 = N'Roles2016.UsersCRM' and c.name = N'Estado'

open constraints
fetch next from constraints into @table_name , @column_name, @constraint_name, @constraint_definition
while @@fetch_status = 0
BEGIN
    print 'CONSTRAINT: ' + @constraint_name
    select @cmd = 'ALTER TABLE [' + @table_name +  '] DROP CONSTRAINT [' +  @constraint_name + ']'
    print @cmd
    EXEC sp_executeSQL @cmd;

  fetch next from constraints into @table_name , @column_name, @constraint_name, @constraint_definition
END

close constraints 
deallocate constraints

扩展Mitch Wheat的代码,下面的脚本将生成删除约束并动态执行它的命令。

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)

下面的解决方案将从表中删除列的特定默认约束

Declare @Const NVARCHAR(256)

SET @Const = (
              SELECT TOP 1 'ALTER TABLE' + YOUR TABLE NAME +' DROP CONSTRAINT '+name
              FROM Sys.default_constraints A
              JOIN sysconstraints B on A.parent_object_id = B.id
              WHERE id = OBJECT_ID('YOUR TABLE NAME')
              AND COL_NAME(id, colid)='COLUMN NAME'
              AND OBJECTPROPERTY(constid,'IsDefaultCnst')=1
            )
 EXEC (@Const)