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.

See also  How to Block Websites During Certain Hours on a Mac Computer

About Our Editorial Process

At DevX, we’re dedicated to tech entrepreneurship. Our team closely follows industry shifts, new products, AI breakthroughs, technology trends, and funding announcements. Articles undergo thorough editing to ensure accuracy and clarity, reflecting DevX’s style and supporting entrepreneurs in the tech sphere.

See our full editorial policy.

About Our Journalist