Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


Tip of the Day
Language: SQL
Expertise: Beginner
Mar 22, 2005

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 ResetIdentityForAllUserTables
as
   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;
   end
close 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.
Jaya Nehru Kumar
 
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap