Easiest / fastest way to Delete ALL records in a Database


--DISABLE ALL CONTRAINTS script
print 'Suspending  CONSTRAINTS for all Tables......'
      Declare @count int
      Declare @rowCount int
      Declare @procName nvarchar(255)
      Declare @dsql nvarchar(500)
      Declare @Enable int
      Set @Enable = 0;
      Set @count=0
      Set @rowCount=0
      select * into #tempProc from sysobjects where xtype = 'u'
      Select @count=Count(*) from #tempProc
      While (@rowCount < @count)
      Begin
            Set @rowCount = @rowCount + 1
            Set @procName = NULL
            Select @procName = name From
            (Select Row_Number() over(order by id) as rowNum, name from #tempProc) as temp
            Where rowNum = @rowCount
            if @Enable =1
                  Set @dsql = 'ALTER TABLE ' +  @procName + ' CHECK CONSTRAINT ALL'
            else
                  Set @dsql = 'ALTER TABLE ' +  @procName + ' NOCHECK CONSTRAINT ALL'
            print @dsql;
            EXEC (@dsql);
      End
drop table #tempProc
GO
 print 'Deleting Tables......'

   DELETE FROM  [Key1]
  DELETE FROM  [Key2]
  DELETE FROM  [KeyP]
  DELETE FROM  [FieldG]
  DELETE FROM  [FieldG]



--ENABLE ALL CONTRAINTS Scripts
print 'Enabling CONSTRAINTS for all Tables......'
      Declare @count int
      Declare @rowCount int
      Declare @procName nvarchar(255)
      Declare @dsql nvarchar(500)
      Declare @Enable int
   Set @Enable = 1;
      Set @count=0
   Set @rowCount=0
      select * into #tempProc from sysobjects where xtype = 'u'
      Select @count=Count(*) from #tempProc
      While (@rowCount < @count)
      Begin
            Set @rowCount = @rowCount + 1
            Set @procName = NULL
            Select @procName = name From
           (Select Row_Number() over(order by id) as rowNum, name from #tempProc) as temp
            Where rowNum = @rowCount
           if @Enable =1
                  Set @dsql = 'ALTER TABLE ' +  @procName + ' CHECK CONSTRAINT ALL'
            else
                  Set @dsql = 'ALTER TABLE ' +  @procName + ' NOCHECK CONSTRAINT ALL'
            print @dsql;
            EXEC (@dsql);
      End
drop table #tempProc
GO

Comments