在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

扩展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 @ObjectName NVARCHAR(100)
SELECT @ObjectName = OBJECT_NAME([default_object_id]) FROM SYS.COLUMNS
WHERE [object_id] = OBJECT_ID('[tableSchema].[tableName]') AND [name] = 'columnName';
EXEC('ALTER TABLE [tableSchema].[tableName] DROP CONSTRAINT ' + @ObjectName)

只需确保columnName周围没有括号,因为查询正在寻找精确匹配,如果它是[columnName]则不会返回任何内容。

这里有一个简单的解决方案,只需替换your_table和column_name。

DECLARE @var0 nvarchar(128)
SELECT @var0 = name
FROM sys.default_constraints
WHERE parent_object_id = object_id(N'${default_schema}.your_table')
  AND col_name(parent_object_id, parent_column_id) = 'column_name';
IF @var0 IS NOT NULL
    EXECUTE ('ALTER TABLE ${default_schema}.your_table DROP CONSTRAINT [' + @var0 + ']');
GO

希望这能对有类似问题的人有所帮助。 在ObjectExplorer窗口中,选择您的数据库=> Tables,=> your table=> Constraints。如果客户是在创建列时定义的,则可以看到包含列名的缺省约束名。 然后使用:

ALTER TABLE  yourTableName DROP CONSTRAINT DF__YourTa__NewCo__47127295;

(约束名只是一个例子)