Home » Tip Bank » Database Development » Sql Server

Language: SQL
Expertise: Beginner

Mar 22, 2005

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.