SQL删除自动命名的约束 [英] SQL remove autonamed constraints
本文介绍了SQL删除自动命名的约束的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我使用脚本在表上创建了一些约束,但未指定约束名称。结果,我最终遇到了诸如 FK__DOC_OBGS ___ kntr ___ 54E63309
这样的约束。
I have created a few constraints on my table with a script without specifying the constraint name. As a result, I ended up having constraints like FK__DOC_OBGS___kntr___54E63309
for example.
是否可以在不指定确切约束名称的情况下删除该约束?
Is it possible to drop that constraints without specifying the exact constraint name ?
例如(不起作用)
ALTER TABLE DOC_OBGS_10 DROP CONSTRAINT LIKE 'FK__DOC_OBGS___kntr%'
问题是我们有很多带有此表的数据库,我需要从表中删除所有约束,但显然名称是每个表都不同。
The problem is that we have a lot of databases with this table and I need to remove all the constraints from the tables, but apparently the name is different for each table.
推荐答案
DECLARE @sql1 NVARCHAR(MAX);
SELECT @sql1 = c
FROM
(
SELECT 'ALTER TABLE DOC_INVS_1 DROP CONSTRAINT ' + CONSTRAINT_NAME + '; '
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_NAME = 'DOC_INVS_1'
and CONSTRAINT_NAME LIKE 'FK__DOC_INVS___kntr%'
) T(c);
EXEC(@sql1);
这篇关于SQL删除自动命名的约束的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文