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:
data observability

Data Observability Explained

Data is the lifeblood of any successful business, as it is the driving force behind critical decision-making, insight generation, and strategic development. However, due to its intricate nature, ensuring the

Heading photo, Metadata.

What is Metadata?

What is metadata? Well, It’s an odd concept to wrap your head around. Metadata is essentially the secondary layer of data that tracks details about the “regular” data. The regular

XDR solutions

The Benefits of Using XDR Solutions

Cybercriminals constantly adapt their strategies, developing newer, more powerful, and intelligent ways to attack your network. Since security professionals must innovate as well, more conventional endpoint detection solutions have evolved