Database Identity property resetter

,

After going through development and test cycles it can be laborious to have to go and reset identity properties on tables (Yawn!!) So I wrote a script to do it. It has two parameters database name and identity start value. It basically checks the identity value with the number of rows in the table. if the identity property doesn't match it resets the identity. Generally a good idea to clear the tables first before running it.

CREATE PROCEDURE proc_resetIdentities
@p_cDatabaseName varchar(50),
@p_nSeedStart int=0
AS
SET NOCOUNT ON
--Declare variables 
DECLARE @strDBName SYSNAME --holds database names
DECLARE @lngDBCount INTEGER --holds database count
DECLARE @lngCounter1 INTEGER --loop counter
DECLARE @strTableName SYSNAME --holds table names
DECLARE @lngTabCount INTEGER --holds table count
DECLARE @lngCounter2 INTEGER --loop counter
DECLARE @strSQL NVARCHAR(4000) --dynamic sql string
DECLARE @lngRecTotal INTEGER		-- total number of records in table
DECLARE @lngIdentValue INTEGER	-- tables identity value
--Create temp tables to hold tables
CREATE TABLE #tTableName
(
numID INTEGER IDENTITY(1,1)
,strTableName SYSNAME
)
-- one database
SET @lngCounter1 = 1
--Populate database name variable
SET @strDBName = @p_cDatabaseName
--Loop through database names
WHILE @lngCounter1 <> 0
BEGIN
	--Populate table names  only use user tables
	SET @strSQL = 'INSERT INTO #tTableName (strTableName)
			  SELECT name FROM ' + @strDBName + '.dbo.sysobjects WHERE xtype =''U'''
	
	EXEC sp_executesql @strSQL
	SET @lngTabCount = (SELECT @@ROWCOUNT) --how many tables in this database
	SET @lngCounter2 = @lngTabCount --Preserve table number for future use
	--Loop through all tables
	WHILE @lngCounter2 <> 0
	BEGIN
		--Populate table name variable
		SET @strTableName = (SELECT strTableName FROM #tTableName WHERE numID = @lngCounter2)
		--Perform action
		CREATE TABLE #tCount
		(totalCount integer)
		CREATE TABLE #tIdentity
		(IdentValue integer)
		--check number of records in table
		SET @strSQL ='INSERT INTO #tcount SELECT COUNT(*) FROM '+@strDbName+'.dbo.' + @strTableName 
		EXEC sp_executesql @strSQL
		SELECT @lngRecTotal=totalCount
		FROM #tCount
		
		DROP TABLE #tCount
		-- get current identity value for table
		SET @strSQL ='INSERT INTO #tIdentity SELECT ident_current('+"'"+@strTableName+"'"+ ')'
		EXEC sp_executesql @strSQL
		SELECT @lngIdentValue=identValue
		FROM #tIdentity
		DROP TABLE #tIdentity
		--check that if zero rows reset identity property
		IF @lngRecTotal=0 AND @lngIdentValue<>0
			BEGIN
			PRINT ''
			PRINT 'Reseeding: '+@strTableName
			PRINT'-------------------------------------------'
			-- reset identity seed
			SET @strSQL ='DBCC checkIdent('+@strTableName+',RESEED,'+CONVERT(varchar,@p_nSeedStart)+')'
			EXEC sp_executesql @strSQL
		END
		--Move backward through tables
		SET @lngCounter2 = @lngCounter2 - 1
	END
	--Move backward through databases
	SET @lngCounter1 = @lngCounter1 - 1
	--Clean out table name temp table
	TRUNCATE TABLE #tTableName
END
DROP TABLE #tTableName

GO

Rate

Share

Share

Rate