SQL Server Truncate All Table Data

1 minute read

Today I want to truncate ( delete all table data) in a large SQL Server database instance. Plain delete statements are not going to work as there is a big number of constraints which do not allow that. After quering the web I found this solution which follows three steps:

  1. Tell SQL Server NOT TO check for constraints for ALL tables
  2. Delete all data from all tables at once
  3. Restore constraint check for ALL tables

To do that I created a small stored procedure. NOTE that it may take a while and reserve quite a lot of CPU and memory to complete.

USE [chris]
GO
-- Object:  StoredProcedure [dbo].[deleteAllTableDatos]    Script Date: 06/01/2012 15:06:14
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      Manios Christos
-- Create date: 2012-06-01
-- Description: Deletes all table data ignoring
--              constraints
-- =============================================
CREATE PROCEDURE [dbo].[deleteAllTableDatos]
    
AS
BEGIN
BEGIN TRANSACTION

EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'

EXEC sp_MSForEachTable 'DELETE FROM ?'

EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'

COMMIT
END

Comments

You May Also Enjoy