Clean your SQL Server database!

Here are some lines of code that can help clean your database from test data so you can start over with a fresh canvas.

/* Disable constraints and triggers (if any) */

exec sp_MSforeachtable ‘ALTER TABLE ? NOCHECK CONSTRAINT ALL’
exec sp_MSforeachtable ‘ALTER TABLE ? DISABLE TRIGGER ALL’

/*Perform delete operation on all table for cleanup, or you can put specific delete <tablename> commands if you want to only do a subset of deletions

*/
exec sp_MSforeachtable ‘DELETE ?’

/*Enable Constraints & Triggers again*/
exec sp_MSforeachtable ‘ALTER TABLE ? CHECK CONSTRAINT ALL’
exec sp_MSforeachtable ‘ALTER TABLE ? ENABLE TRIGGER ALL’

/*Reset Identity on tables with identity column*/
exec sp_MSforeachtable ‘IF OBJECTPROPERTY(OBJECT_ID(”?”), ”TableHasIdentity”) = 1 BEGIN DBCC CHECKIDENT (”?”,RESEED,0) END’

Tags: SQL Server, T-SQL