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.
-- 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:
- Delete All Data T SQL Script
- sp_MsForEachTable create script for Azure
- Initial idea for such delete script from 2006 year : vadivel’s post.
- sp_MSForEachTable Azure equivalent : https://gist.github.com/metaskills/893599