Before you start testing any database-driven application, the first thing you need to do is:
- Remove the old data from all the tables.
- 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.