truncating all tables in DB with foriegn key constraints

truncate_tables_with_constraints.sql

--http://stackoverflow.com/questions/155246/how-do-you-truncate-all-tables-in-a-database-using-tsql

--When dealing with deleting data from tables which have foreign key relationships - which is basically the -----case with any properly designed database - we can disable all the constraints, delete all the data and then ---re-enable constraints


-- disable all constraints
EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"

-- delete data in all tables
EXEC sp_MSForEachTable "DELETE FROM ?"

-- enable all constraints
exec sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"

--if some of the tables have identity columns we may want to reseed(start identity element from 1) them

EXEC sp_MSforeachtable "DBCC CHECKIDENT ( '?', RESEED, 0)"