Reseed database tables’ identity


I ran into reseed an identity field question here: http://forums.asp.net/t/1272315.aspx
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: http://jingyang.spaces.live.com/blog/cns!CC21A118B1B5250!224.entry

Advertisements


Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s