SQL Server Truncate All Table Data

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
Advertisements

About cmanios

programming
This entry was posted in Databases, Sql Server and tagged , , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s