T SQL Script to delete all data from database

Empty-StoreTomasz Modelski

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.

In my current project I use it in [ClassInitialize] of my MS Unit Tests ( [TestFixtureSetup] in NUnit ), or it can be used as well before each individual test (MS [TestInitialize], NUnit [SetUp] ) or wherever you need it.

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.

Downloads & related links:

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