T-SQL Script to delete all data from database

In my work – when developing database based applications – I really often need a T-SQL script or procedure to delete all data from database while preserving all database structure, objects, foreign keys, indexes, etc.

Mostly I use this feature in integration tests – clean up db & create initial testing environment (fill db with meta data, test data, etc.) and launch my tests.

Requirements for such script are simple: work on any database structure, remove all data, leave structure unchanged,  be fast, work on latest MS SQL versions (2008, 2012, 2014) & Azure.

Solution

-- 1) Disable referential integrity 
EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL' 

-- 2) Core delete/truncate 
EXEC sp_MSForEachTable ' 
    SET QUOTED_IDENTIFIER ON 
    IF ''?'' <> ''[dbo].[sysdiagrams]'' 
    BEGIN 
        IF OBJECTPROPERTY(object_id(''?''), ''TableHasForeignRef'') = 1 
             DELETE FROM ? 
        ELSE TRUNCATE TABLE ? 
    END '

-- 3) Restore referential integrity 
EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'

First: we are using undocumented system stored procedure sp_MSForEachTable to disable constraints on all tables.
Some references about sp_MSForEachTable : Technet wiki, wiki.lessthandot.com .
If it’s not existing in your database, it could be  created manually (see below).

Second: with sp_MSForEachTable help we are invoking the ‘core’ of our script :
DELETE FROM [Table] or TRUNCATE TABLE [Table].
Truncate Table is faster but MS SQL won’t execute Truncate on any table referenced from other tables by foreign keys.
(see msdn  + there are other specific limitations).
There’s another catch (first IF statement ) – we don’t want to remove data from sysdiagrams table – it holds data about created diagrams.

Third: enabling back all constraints … and the required work is done.
Caution: above script won’t work on Azure database, because it’s missing sp_MSForEachTable,
but such procedure can be created manually on Azue – see downloads section.

Remarks, downloads & related links:

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