使用循环删除块中的多个表 [英] Deleting multiple tables in chunks using loop

问题描述

我要求删除  100张桌子的数据 它们驻留在生产环境中同一服务器下的不同数据库中。有一些巨大的表，其数据超过5000万。

这些表应该按依赖顺序删除（即它们有外键）。我想知道是否有任何可用的脚本可以使用一次一个表的块循环以最佳方式删除数据？非常感谢你的帮助。

解决方案

achtro，

依靠递归来搜索整个"外键树"。从根表开始，并将信息记录到表中，然后我们将从叶级别
表开始遍历此表，并找到相应的FK，然后组成delete语句。

 

有一个类似的问题，请参考：

https://www.mssqltips.com/sqlservertip/4059/script-to-delete-data-from-sql-server- table-with-foreign-key-constraints /

 

顺便说一下，如果您只有100个具有相互外键约束的表，并且您想要全部删除在这100个表中的数据，您可以使外键约束无效首先，然后
删除所有数据。

  - 禁用所有表约束
 ALTER TABLE YourTableName NOCHECK CONSTRAINT ALL 
  - 启用所有表约束
 ALTER TABLE YourTableName CHECK CONSTRAINT ALL 
  -  ---------- 
  - 禁用单个约束
 ALTER TABLE YourTableName NOCHECK CONSTRAINT YourConstraint 
  - 启用单个约束
 ALTER TABLE YourTableName CHECK CONSTRAINT YourConstraint 
  -  ---------- 
  - 禁用数据库的所有约束
 EXEC sp_msforeachtable " ALTER TABLE？NOCHECK CONSTRAINT all" 
  - 启用数据库的所有约束
 EXEC sp_msforeachtable" ALTER TABLE？WITH CHECK CHECK CONSTRAINT all" 

希望它可以帮到你。

 

最好的问候，

拉结

Hi ,

I have a requirement to delete  data from 100 tables  which are residing on different databases under same server in production environment.There are some huge tables which has data beyond 50 million .

These tables should be deleted in dependency order (i.e they have foreign keys). I was wondering if there is any script available that can delete the data in a optimal way using a loop in chunks with one table at a time? your help is much appreciated.

解决方案

Hi achtro,

Rely on recursion to search the whole "foreign key tree" starting from the root table, and log the information to a table, then we will loop through this table starting with the leaf level tables, and find the corresponding FKs and then compose the delete statement.

 

There is an similar issue , please refer to it : https://www.mssqltips.com/sqlservertip/4059/script-to-delete-data-from-sql-server-tables-with-foreign-key-constraints/

 

By the way , If you just have 100 tables with mutual foreign key constraints, and you want to delete all the data in those 100 tables, you can invalidate the foreign key constraint first and then delete all the data.

-- Disable all table constraints
ALTER TABLE YourTableName NOCHECK CONSTRAINT ALL
-- Enable all table constraints
ALTER TABLE YourTableName CHECK CONSTRAINT ALL
-- ----------
-- Disable single constraint
ALTER TABLE YourTableName NOCHECK CONSTRAINT YourConstraint
-- Enable single constraint
ALTER TABLE YourTableName CHECK CONSTRAINT YourConstraint
-- ----------
-- Disable all constraints for database
EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"
-- Enable all constraints for database
EXEC sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"

Hope it can help you.

 

Best Regards,

Rachel

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