SQL Server 2005, Clean your Database Records & reset Identity Columns, all in 6 lines!
Well, I had a small issue regarding writing a script to clean a
database we have and reset its identity columns in all tables. Although
the database wasn't huge one (less than 100 tables) I had to trace
relations to be able to delete child table's records before parent's
ones because of the foreign key constraints. The solution is disable
the foreign keys and delete records with no fear of any errors then
enables the constraints again.
Well, I found a solution to disable
all constraints without the need to go on each table and disable it
manually. and I was happy to know that I can use this solution in
deleting all records from all tables. Not only this I was able to use
the same solution to reset identity columns in all tables.
The solution was to use this built in stored procedure sp_MSforeachtable. For help about this proc search for it in Books online or use this sp_helptext sp_MSForeachtable.
Now back to my 6 lines, bellow is how I re-zeroed my Database:
/*Disable Constraints & Triggers*/
exec sp_MSforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
exec sp_MSforeachtable 'ALTER TABLE ? DISABLE TRIGGER ALL'
/*Perform delete operation on all table for cleanup*/
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'
