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
End of Article
