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:
- Tell SQL Server NOT TO check for constraints for ALL tables
- Delete all data from all tables at once
- 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