删除表中的所有约束 [英] Drop All constraints in a Table

问题描述

我试图编写脚本删除约束。

Am trying to write script for removing Constraints.

我有以下函数选择Constarints在我的DataBase

I have the below function to select the Constarints in my DataBase

SELECT  name
    FROM sys.foreign_keys

我已使用上述脚本编写了alter脚本

And I have written alter scripts using the above scripts

SELECT 
    'ALTER TABLE ' + OBJECT_NAME(parent_object_id) + 
    ' DROP CONSTRAINT ' + name
FROM sys.foreign_keys

上面的查询如何执行这些约束？

Using the above query how can I execute these constraints ?

我可以使用 DROP DATABASE DBName 。但是我只是试图通过删除约束删除表。

I can use DROP DATABASE DBName. But am just trying to drop tables by dropping Constraints.

是不是可能不去SP？

is it possible without going for SP ? Or any easy ways I can proceed?

推荐答案

您可以随时从底部窗格复制输出，将其粘贴到顶部窗格，然后单击F5。或者你可以建立一个字符串直接执行：

Well you can always copy the output from the bottom pane, paste it into the top pane, and hit F5. Or you can build a string to execute directly:

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += N'
ALTER TABLE ' + QUOTENAME(OBJECT_SCHEMA_NAME(parent_object_id))
    + '.' + QUOTENAME(OBJECT_NAME(parent_object_id)) + 
    ' DROP CONSTRAINT ' + QUOTENAME(name) + ';'
FROM sys.foreign_keys;

PRINT @sql;
-- EXEC sp_executesql @sql;

（当您对 PRINT 输出，注释它并取消注释 EXEC 。注意，打印输出将在Management Studio中截断为8K，但变量真正保存整个命令。）

(When you are happy with the PRINT output, comment it out and uncomment the EXEC. Note that the print output will be truncated to 8K in Management Studio but the variable really holds the entire command.)

另外，我不知道这真的与你是否使用存储过程有关，或者为什么你试图这样做w / o去SP。 。这个查询可以作为一个存储过程运行，也可以不是，这一切取决于你打算调用它的频率，过程存在的地方等。

Also I don't know how this really relates to whether you are using a stored procedure or not, or why you are trying to do it "w/o going for SP"... this query can be run as a stored procedure or not, it all depends on how often you're going to call it, where the procedure lives, etc.

这篇关于删除表中的所有约束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！