Reseed database tables’ identityPosted: June 9, 2008
EXEC [sp_MSforeachtable] @command1="TRUNCATE TABLE ?" –Remove all data your tables and reset the identity and ready to go
EXEC sp_MSforeachtable ‘IF OBJECTPROPERTY(OBJECT_ID(”?”), ”TableHasIdentity”) = 1 BEGIN DBCC CHECKIDENT (”?”,RESEED,100) END’
All tables will start with 100 for their identity fields.
If there is a Foreign key relation between two table, you need to drop the relation first and recreate the relation after you are done.
Here is link to a sample for how to do it: http://jingyang.spaces.live.com/blog/cns!CC21A118B1B5250!224.entry