On most development projects I create a database initialization script that clears data from all tables then inserts a fresh set of sample data into the database. There are several approaches to take but I’ve never seen this one before today. I like it because I don’t have to script the database (and keep that script up to date) and it’s concise. It turns out there’s an undocumented system sp_ procedure that does the heavy lifting. Here’s the script:
--Remove table constraints
EXEC sp_MSForEachTable 'Alter Table ? NoCheck Constraint All'
--Clear data from each table
EXEC sp_MSForEachTable
'
DECLARE @MSG nvarchar(4000)
If CHARINDEX(''[dbo].[sys'', ''?'') = 0
BEGIN
If ObjectProperty(Object_ID(''?''), ''TableHasForeignRef'') = 1
BEGIN
-- Just to know which tables used delete syntax.
SET @MSG = ''DELETE FROM '' + ''?''
RAISERROR(@MSG,0,1) WITH NOWAIT
DELETE FROM ?
END
ELSE
BEGIN
-- Just to know which tables used Truncate syntax.
SET @MSG = ''TRUNCATE TABLE '' + ''?''
RAISERROR(@MSG,0,1) WITH NOWAIT
TRUNCATE TABLE ?
End
End
'
--Restore table constraints
Exec sp_MSForEachTable 'Alter Table ? Check Constraint All'
