华为云计算 云知识 MSSQL 删除数据库表数据

MSSQL 删除数据库表数据

--删除数据库表数据  慎用
create PROCEDURE sp_DeleteAllData
AS declare @tblName nvarchar(50)
  declare @sNOCHECKCONSTRAINT nvarchar(500)
  declare @sDISABLETRIGGER nvarchar(500)
  declare @sDeleteTable nvarchar(500)
  declare @sCHECKCONSTRAINT nvarchar(500)
  declare @sENABLETRIGGER nvarchar(500) begin try begin tran -- 失效索引,触发器 declare tb cursor for select name from sys.sysobjects where xtype='U' and category=0 open tb fetch next from tb into @tblName while @@fetch_status=0 begin set @sNOCHECKCONSTRAINT ='ALTER TABLE ' + @tblName+ ' nocheck CONSTRAINT ALL' set @sDISABLETRIGGER ='ALTER TABLE ' + @tblName+ ' DISABLE TRIGGER ALL' EXEC sp_MSForEachTable @sNOCHECKCONSTRAINT EXEC sp_MSForEachTable @sDISABLETRIGGER fetch next from tb into @tblName end close tb deallocate tb -- 删除数据 declare tb1 cursor for select name from sys.sysobjects where xtype='U' and category=0 open tb1 fetch next from tb1 into @tblName while @@fetch_status=0 begin set @sDeleteTable ='delete from ' + @tblName EXEC sp_MSForEachTable @sDeleteTable print '删除数据表'+@tblName +'完成' fetch next from tb1 into @tblName end close tb1 deallocate tb1 --恢复索引,触发器 declare tb2 cursor for select name from sys.sysobjects where xtype='U' and category=0 open tb2 fetch next from tb2 into @tblName while @@fetch_status=0 begin set @sCHECKCONSTRAINT   ='ALTER TABLE ' + @tblName+ ' check CONSTRAINT ALL' set @sENABLETRIGGER ='ALTER TABLE ' + @tblName+ ' ENABLE TRIGGER ALL' EXEC sp_MSForEachTable @sCHECKCONSTRAINT EXEC sp_MSForEachTable @sENABLETRIGGER fetch next from tb2 into @tblName end close tb2 deallocate tb2 commit tran
  end try
  begin catch rollback
  end catch
GO 

 

转载于:https://www.cnblogs.com/adsoft/p/11475679.html

上一篇:html状态码

下一篇:LeetCode114. 二叉树展开为链表

51CTO

CSDN

中国开发者社区CSDN (Chinese Software Developer Network) 创立于1999年,致力为中国开发者提供知识传播、在线学习、职业发展等全生命周期服务。
相关推荐