如何从 SQL Server 数据库中的所有表中删除所有行? [英] How to delete all rows from all tables in a SQL Server database?

问题描述

如何删除 SQL Server 数据库中所有表中的所有行?

How to delete all rows from all tables in a SQL Server database?

推荐答案

请注意，如果您设置了任何参照完整性，则 TRUNCATE 将不起作用.

Note that TRUNCATE won't work if you have any referential integrity set.

在这种情况下，这将起作用:

In that case, this will work:

EXEC sp_MSForEachTable 'DISABLE TRIGGER ALL ON ?'
GO
EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
GO
EXEC sp_MSForEachTable 'DELETE FROM ?'
GO
EXEC sp_MSForEachTable 'ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL'
GO
EXEC sp_MSForEachTable 'ENABLE TRIGGER ALL ON ?'
GO

明确地说，语句中的?是一个?.它被 sp_MSForEachTable 过程替换为表名.

To be clear, the ? in the statements is a ?. It's replaced with the table name by the sp_MSForEachTable procedure.

这篇关于如何从 SQL Server 数据库中的所有表中删除所有行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！