Tsql – Delete all rows from tables in database – fast

Posted: 2012-12-07 in Sql
Tags: ,

Simple way to cleanse/delete all data in all tables in a database fast.

If you have constraints you might have to run it several times.

 


-- Truncate all tables
DECLARE @name VARCHAR(128)
DECLARE @SQL VARCHAR(254)

SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'U' AND category = 0 ORDER BY [name])

WHILE @name IS NOT NULL
BEGIN
 SELECT @SQL = 'TRUNCATE TABLE [dbo].[' + RTRIM(@name) +']'
 EXEC (@SQL)
 PRINT 'Truncated Table: ' + @name
 SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'U' AND category = 0 AND [name] > @name ORDER BY [name])
END
GO

Cheers

Choco

 

Advertisements

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