Reseed database tables’ identity

I ran into reseed an identity field question here:
To make the reseed work properly, we need to truncate all tables in the database.
I’ll use the undocumented sp_MSforeachtable to do this for now.

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:!CC21A118B1B5250!224.entry


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s