Before you start testing any database-driven application, the first thing you need to do is:

1. Remove the old data from all the tables.
2. Reset the identity back to 1 for every one of the tables.

If you’ve got a lot of tables, this process can get quite tedious.

The following is a stored procedure which automates the whole process:

create procedure ResetIdentityForAllUserTablesas   declare @tblName varchar(30);   declare @SqlQuery varchar(3000);   declare curResetIdentity cursor for SELECT name FROM sysobjects       WHERE (type = 'U') AND name <> 'dtproperties';   open curResetIdentity;   fetch next from curResetIdentity into @tblName;   while (@@Fetch_Status=0)   begin      set @SqlQuery = 'truncate table ' + @tblName;      execute (@SqlQuery);      if (@tblName <> '')      begin         set @SqlQuery = 'dbcc checkident(' + @tblName + ',reseed,1)';         execute (@SqlQuery);      end      fetch next from curResetIdentity into @tblName;   endclose curResetIdentity;Deallocate curResetIdentity;

Note: Remember that this affects all the user tables in the database. So if you’re working with a test database, where not all the tables belong to the application you’re testing, this tip resets all the counters.