--删除数据库表数据  慎用

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