如何删除数据库中所有表的所有记录?我需要使用一个 SQL 命令还是每个表都需要一个 SQL 命令?
如果可能的话,使用TRUNCATE而不是DELETE可以节省时间/空间,而且在你有一个巨大的数据库的情况下,不会使日志文件膨胀。
EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
GO
EXEC sp_MSForEachTable '
IF OBJECTPROPERTY(object_id(''?''), ''TableHasForeignRef'') = 1
DELETE FROM ?
ELSE
TRUNCATE TABLE ?'
EXEC sp_MSForEachTable 'ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL'
GO
结合现有答案中的所有优秀技巧以及其他一些技巧,我编写了这个脚本。它更加全面,效率应该更高。
在删除步骤之前,脚本会执行以下操作:
对于删除步骤,它将尽可能地截断(速度更快),否则使用tablock提示进行删除(tablock仅对堆表有帮助,并且由于我们无论如何都会收缩文件,因此在这里可能不会添加任何内容)。
在删除步骤之后,脚本将执行以下操作:
删除数据库中的所有数据:
use [your_database]
go
exec sp_MSforeachtable 'alter table ? disable trigger all'
go
exec sp_MSforeachtable 'alter table ? nocheck constraint all'
go
-- Disable enabled nonclustered indices
declare @script nvarchar(max)
declare cr cursor fast_forward read_only for
select 'alter index ' + quotename(i.name) + ' on ' + quotename(schema_name(t.schema_id))+'.'+ quotename(t.name) + ' disable'
from sys.indexes i inner join sys.tables t on i.object_id = t.object_id
where i.type_desc = 'nonclustered' and i.name is not null and i.is_disabled = 0;
open cr
fetch next from cr into @script
while @@fetch_status = 0
begin
execute sp_executesql @script
fetch next from cr into @script
end
close cr
deallocate cr
go
exec sp_MSforeachtable 'set quoted_identifier on; if objectproperty(object_id(''?''), ''TableHasForeignRef'') = 1 delete from ? with (tablock) else truncate table ?'
go
exec sp_MSforeachtable 'set quoted_identifier on; alter index all on ? rebuild';
go
exec sp_MSforeachtable 'alter table ? with check check constraint all'
go
exec sp_MSforeachtable 'alter table ? enable trigger all'
go
-- Re-seed identity columns
exec sp_MSforeachtable 'if objectproperty(object_id(''?''), ''TableHasIdentity'') = 1 dbcc checkident(''?'', reseed, 0)'
go
-- Shrink the database files
declare @db_name nvarchar(200) = db_name()
dbcc shrinkdatabase (@db_name, 0);
go
是的,可以用一行代码删除
SELECT 'TRUNCATE TABLE ' + d.NAME + ';'
FROM sys.tables d
WHERE type = 'U'