Reset Identity Values

I wrote a stored procedure, ResetIdentities, that "reseeds" all the identity columns in all tables on all database schemas to the appropriate ident_seed values (i.e., the initial identity values). This procedure is useful if the development database is full of test data. When all the unnecessary data is deleted, the identity values still have the current value for the next identity (which is typically the maximum value reached, plus one).

To globally reset all identity values in the entire database to their initial seed value, the ResetIdentities stored procedure dynamically executes the DBCC CHECKIDENT T-SQL statement with the RESEED option enabled. You can download the ResetIdentities.sql file from SQL Server Magazine's Web site. (Go to http://www.sqlmag.com, enter 95763 in the InstantDoc ID text box, then click the 95763.zip hotlink.) To run this procedure, create it in each user database, then enter the following command from inside the user database:

exec ResetIdentities

I tested the stored procedure on SQL Server 2005 SP1 and SQL Server 2000 SP1.
—Eli Leiba, Senior Application DBA, Israel Electric Company

See Associated Figure