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