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.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
|
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
|