如何删除 SQL Server 表列表，忽略约束？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1720755/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to drop a list of SQL Server tables, ignoring constraints?
提问by Joannes Vermorel
I have a list of half a dozen MSSQL 2008 tables that I would like to remove at once from my database. The data has been entirely migrated to new tables. There is no reference in the newtables to the oldtables.
我有一个包含六个 MSSQL 2008 表的列表，我想立即从我的数据库中删除它们。数据已完全迁移到新表。新表中没有对旧表的引用。
The problem being that old tables comes with loads of inner FK constraints that have been autogenerated by a tool (aspnet_regsql actually). Hence dropping manually all constraints is a real pain.
问题是旧表带有大量由工具（实际上是 aspnet_regsql）自动生成的内部 FK 约束。因此手动删除所有约束是一个真正的痛苦。
How can I can drop the old tables ignoring all inner constraints?
如何删除旧表而忽略所有内部约束？
回答by Naveed Parvez
It depends on how you want to drop the tables. If list of tables need to drop covers almost above 20 % of tables under your DB.
这取决于您想如何删除表。如果表列表需要删除几乎超过 20% 的表在您的数据库下。
Then I will disable all the constraints in that DB under my script and drop the tables and Enable the constraints under the same script.
然后我将在我的脚本下禁用该数据库中的所有约束，并删除表并在同一脚本下启用约束。
--To Disable a Constraint at DB level
EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
--Write the code to DROP tables
DROP TABLE TABLENAME
DROP TABLE TABLENAME
DROP TABLE TABLENAME
--To Enable a Constraint at DB level
EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
Finally to check the Status of your constraints fire up this Query.
最后要检查约束的状态，请启动此查询。
--Checks the Status of Constraints
SELECT (CASE
WHEN OBJECTPROPERTY(CONSTID, 'CNSTISDISABLED') = 0 THEN 'ENABLED'
ELSE 'DISABLED'
END) AS STATUS,
OBJECT_NAME(CONSTID) AS CONSTRAINT_NAME,
OBJECT_NAME(FKEYID) AS TABLE_NAME,
COL_NAME(FKEYID, FKEY) AS COLUMN_NAME,
OBJECT_NAME(RKEYID) AS REFERENCED_TABLE_NAME,
COL_NAME(RKEYID, RKEY) AS REFERENCED_COLUMN_NAME
FROM SYSFOREIGNKEYS
ORDER BY TABLE_NAME, CONSTRAINT_NAME,REFERENCED_TABLE_NAME, KEYNO
If you dont want to disable the constraints at Database level then make a list of tables which you want to drop.
如果您不想在数据库级别禁用约束，请列出要删除的表。
Step1 : Check the Constraints associated with thos tables
步骤 1：检查与 thos 表关联的约束
SELECT *
FROM sys.foreign_keys
WHERE referenced_object_id = object_id('dbo.Tablename')
Step2 : Disable the Constraints which are associated with these tables.
步骤 2：禁用与这些表关联的约束。
ALTER TABLE MyTable NOCHECK CONSTRAINT MyConstraint
Step3 : Drop the tables
第3步：删除表
DROP TABLE TABLENAME
回答by Venkatesh Muniyandi
Run the following script to delete all the constraints in all tables under current DB and then run the drop table statements.
运行以下脚本删除当前DB下所有表的所有约束，然后运行drop table语句。
DECLARE @dropAllConstraints NVARCHAR(MAX) = N'';
SELECT @dropAllConstraints += N'
ALTER TABLE ' + QUOTENAME(OBJECT_SCHEMA_NAME(parent_object_id))
+ '.' + QUOTENAME(OBJECT_NAME(parent_object_id)) +
' DROP CONSTRAINT ' + QUOTENAME(name) + ';'
FROM sys.foreign_keys;
EXEC sp_executesql @dropAllConstraints
回答by gbn
A simple DROP TABLE dbo.MyTablewill ignore all constraints (and triggers) except foreign keys (unless you drop the child/referencing table first) where you may have to drop these first.
一个简单的DROP TABLE dbo.MyTable将忽略所有约束（和触发器），除了外键（除非您先删除子/引用表），您可能必须先删除这些。
Edit: after comment:
编辑：评论后：
There is no automatic way. You'll have to iterate through sys.foreign_keysand generate some ALTER TABLE statements.
没有自动方式。您必须遍历sys.foreign_keys并生成一些 ALTER TABLE 语句。
回答by Jonathan Arkell
I found a reasonable(ish) way to do it by making SQL write the SQL to drop the constraints:
我找到了一种合理的（ish）方法，通过让 SQL 编写 SQL 来删除约束：
select concat("alter table ", table_name, " drop ", constraint_type ," ", constraint_name, ";")
from information_schema.table_constraints
where table_name like 'somefoo_%'
and
constraint_type <> "PRIMARY KEY";
You will want to modify the table name to suit your needs, or possibly select against other column/values.
您需要修改表名以满足您的需要，或者可能针对其他列/值进行选择。
Also, this would select any non primary key constraint, which might be too big of a sledgehammer. Maybe you need to just set it to =?
此外，这将选择任何非主键约束，这可能太大了。也许您需要将其设置为 =？
I am not a DBA. there may be better ways to do this, but it worked well enough for my purposes.
我不是 DBA。可能有更好的方法来做到这一点，但它对我的目的来说已经足够好了。
回答by Joannes Vermorel
I finally found the solution based on the script provided by Jason Presley. This script automatically removes all constraints in the DB. It's easy to add a WHERE clause so that it only applies to the set of concerned tables. After that, dropping all tables is a straightforward.
我终于根据Jason Presley 提供的脚本找到了解决方案。此脚本会自动删除数据库中的所有约束。很容易添加 WHERE 子句，以便它仅适用于相关表的集合。之后，删除所有表就很简单了。
回答by jeff porter
I suspect that you would have to do an 'alter' command on the offending tables before the drop to remove the forigen key contraints.
我怀疑您必须在删除之前对违规表执行“更改”命令才能删除外国密钥限制。
ALTER TABLE Orders DROP FOREIGN KEY fk_PerOrders;
DROP TABLE Orders;
Of course if you drop the child tables first, then you wont have this problem. (unless you have table A contraint to table B and table B constraint to A, then you will need to Alter one of the tables, e.g. A to remove the constraint)
当然，如果你先删除子表，那么你就不会遇到这个问题。（除非您将表 A 约束到表 B 并将表 B 约束到 A，否则您将需要更改其中一个表，例如 A 以删除约束）
e.g. this WONT work, since Orders has a contraint from Order_Lines
例如这不会工作，因为订单有来自 Order_Lines 的约束
DROP TABLE Orders;
DROP TABLE Order_lines;
e.g. this will work
例如这会起作用
DROP TABLE Order_lines;
DROP TABLE Orders;
