Reset the Identity for All User Tables

Reset the Identity for All User Tables

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.


Share the Post: