如何在不知道其名称的情况下删除 SQL 默认约束?

问题描述

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

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

但由于之前版本的数据库中存在拼写错误，约束的名称可能是 DF_SomeTable_ColName 或 DF_SmoeTable_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.

如何在没有任何 SQL 错误的情况下删除默认约束?默认约束名称不会显示在 INFORMATION_SCHEMA 表中，这让事情变得有点棘手.

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.

因此，例如删除此表/列中的默认约束"或删除DF_SmoeTable_ColName"，但如果找不到，请不要给出任何错误.

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.

推荐答案

在 Mitch Wheat 的代码上展开，以下脚本将生成删除约束并动态执行的命令.

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

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)

这篇关于如何在不知道其名称的情况下删除 SQL 默认约束?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！