SQL语句清空所有table中的记录

最新推荐文章于 2023-04-12 16:41:33 发布

iteye_6251

最新推荐文章于 2023-04-12 16:41:33 发布

阅读量210

点赞数

文章标签： SQL Go

本文链接：https://blog.csdn.net/iteye_6251/article/details/82009969

版权

-----此处不用truncate是因为truncate必须得删除外键,如果不考虑外键的话最好用truncate

----禁用当前数据库中所有表的约束、触发器
exec sp_msforeachtable 'ALTER TABLE ? DISABLE TRIGGER all'
exec sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT all'

go

----清空除employee、department、account外的所有表中的记录
begin
declare crsr cursor ------定义游标取回用户建立的表
for SELECT [name] FROM DBO.SYSOBJECTS
WHERE OBJECTPROPERTY(ID,N'IsTable')=1 and type = 'U' and ([name] <> 'dtproperties'
and [name] <> 'account' and [name] <> 'employee' and [name]<>'department' )

declare @tblName sysname ------用户表名

open crsr
fetch crsr into @tblName
EXEC(' delete from dbo.'+@tblName)
print @tblName
while @@fetch_status=0
begin
fetch next from crsr into @tblName
exec('delete from dbo.'+@tblName)
print @tblName
end
close crsr
deallocate crsr
end

go

----启用当前数据库中所有表的约束、触发器
exec sp_msforeachtable 'ALTER TABLE ? enable TRIGGER all'
exec sp_msforeachtable 'ALTER TABLE ? CHECK CONSTRAINT all'