Reset Identity Column with sp_IdentReSeed

,

Getting tired of dropping tables just re-seed the identity column? Remember, TRUNCATE TABLE doesn't work with foreign keys on tables.

--EXAMPLE:
DELETE FROM MyTable
sp_Reseed 'MyTable'

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROC sp_ReSeed(@vTableName VARCHAR(255) = NULL)
AS 
BEGIN
   /***
    *   Date:         
    *   Author:       <mailto:mikemcw@4segway.biz>
    *   Project:      Fun stuff for developers and dba's
    *   Location:     User Database
    *   Permissions:  PUBLIC EXECUTE
    *   
    *   Description:  resets the identity column
    ***/

   SET NOCOUNT ON
   DECLARE @vRET      VARCHAR(255)
   DECLARE @nRowCount INT
   DECLARE @vSQL      NVARCHAR(150)

   CREATE TABLE #rowcount(nRowCount INT)

   SET @vSQL = 'SELECT COUNT(*) [RowCount] FROM ' + object_name(object_id('item'))
   INSERT INTO #Rowcount  EXECUTE sp_executesql @vSQL
   SELECT @nRowCount = nRowCount FROM #RowCount

   IF @nRowCount > 0 
      BEGIN
         SELECT 'Table must be empty first'
         DROP TABLE #rowcount
         RETURN
      END
   DROP TABLE #rowcount

   IF @vTableName IS NULL OR 
      LTRIM(RTRIM(@vTableName )) = '' OR
      LTRIM(RTRIM(@vTableName )) = '?'  GOTO Usage 

   IF 0 < CHARINDEX ('.' ,@vTableName) GOTO UserError

   DBCC CHECKIDENT (@vTableName, RESEED, 0)
   IF @@ERROR = 0 
   SELECT @@ERROR 'Return'
   RETURN

UserError:
   SET @vRet = 'db.owner.table format not supported.' + CHAR(13)
   SET @vRet = @vRet + 'Table must exist in current database.' + CHAR(13)
   SELECT -1 'Return'  , @vRet 'Descripiton'
   RETURN

Usage:
   Print 'sp_ReSeed'
   Print '  Description: Resets identity column of a table'
   Print '  Requirement: Table MUST be empty for expected results.'
   Print ''
   Print '  Parameters:  @vTableName Varchar(255)'
   Print '               ie.  statusstate'
   Print 'Example:  sp_ReSeed ''MyTable'''
END
GO
GRANT EXECUTE ON sp_ReSeed TO PUBLIC
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

Rate

Share

Share

Rate