--**************************************
--     
-- Name: Renumbering identity columns V2
--     .0 in one stored procedure
-- Description:Renumbering identity colu
--     mns V2.0 in one stored procedure.
-- By: Zoltan Tamas, Toth
--
-- Inputs:EXEC Renum_Identity_Column
@i_Table_Name = 'Ident' --Table name
@i_Ident_seed = 1,
@i_Ident_Increment = 1
--
--This code is copyrighted and has-- limited warranties.Please see http://
--     www.Planet-Source-Code.com/vb/scripts/Sh
--     owCode.asp?txtCodeId=486&lngWId;=5--for details.--**************************************
--     

------------
-- //////////////////////////////
-- // sp_Renum_Identity_Column //
-- //////////////////////////////
IF EXISTS (SELECT name FROMsysobjects WHERE name = N'Renum_Identity_Column' AND type = 'P')
DROP PROCEDURE Renum_Identity_Column
GO
CREATE PROCEDURE Renum_Identity_Column 
 @i_Table_Name nvarchar(1000),
 @i_Ident_seed int = 1,
 @i_Ident_Increment int = 1
AS
 -- Created by Zoltan Tamas Toth on 2002
--     .06.05.
 -- Last updated by Zoltan Tamas Toth on
--      2002.06.06.
 SET NOCOUNT ON
 DECLARE 
@p_Dynamic_SQL nvarchar(4000)
 -- Allow ad hoc update to system catalo
--     g
 SET @p_Dynamic_Sql = '
 EXEC sp_configure ''allow update'', ''1'' 
 '
 EXEC master..sp_executesql @p_Dynamic_Sql
 SET @p_Dynamic_Sql = '
 RECONFIGURE WITH OVERRIDE 
 '
 EXEC master..sp_executesql @p_Dynamic_Sql
 -- Reset identity property of the colum
--     n 
 DECLARE 
 @tablename varchar(128), 
 @colname varchar(128), 
 @tableid int, 
 @ident_seed int, 
 @ident_incr int 
 SET @tablename = @i_Table_name
 SELECT @tableid = OBJECT_ID(@tablename) 
 SELECT @ident_seed = IDENT_SEED(@tablename) 
 SELECT @ident_incr = IDENT_INCR(@tablename) 
 -- Find the identity column. 
 SELECT @colname = name FROM syscolumns WHERE id = @tableid AND (colstat & 1 <> 0) 
 -- Reset identity 
 UPDATE syscolumns SET colstat = colstat & ~CAST(0x01 AS smallint) 
 WHERE id = @tableid AND name = @colname 
 ---------------------------------------
----------------
 -- Renumbering Identity column
 IF @colname IS NULL 
     BEGIN
    SELECT 'Table (' + @tablename + ') hasn''t got IDENTITY column!'
 END

 ELSE BEGIN
--IF @i_Order_Column IS NULL SET @i_Orde
--     r_Column = @colname
SET @p_Dynamic_SQL = '
 DECLARE
@p_colname int,
@p_i int
 SET @p_i = ' + CONVERT ( nvarchar(20), @i_Ident_seed ) + '
 DECLARE #cur_renumbering CURSOR FOR
SELECT
 ' + @colname + ' 
FROM
 ' + @Tablename + '
ORDER BY
 ' + @Colname + ' 
 OPEN #cur_renumbering
 FETCH NEXT FROM #cur_renumbering INTO @p_colname
 WHILE @@FETCH_STATUS = 0 
     BEGIN
    UPDATE 
     ' + @tablename + ' 
    SET 
     ' + @colname + ' = @p_i
    WHERE 
     ' + @colname + ' = @p_colname 
    SET @p_i = @p_i + ' + CONVERT ( nvarchar(20), @i_Ident_Increment ) + '
    FETCH NEXT FROM #cur_renumbering INTO @p_colname
 END

 CLOSE #cur_renumbering
 DEALLOCATE #cur_renumbering
 '
 EXEC ( @p_Dynamic_Sql )
END

 ---------------------------------------
----------------
 -- Set identity 
 UPDATE syscolumns SET colstat = colstat | CAST(0x01 AS smallint) 
 WHERE id = @tableid AND name = @colname 
 SET @p_Dynamic_Sql = '
 EXEC sp_configure ''allow update'', ''0'' 
 '
 EXEC master..sp_executesql @p_Dynamic_Sql
 --------------
 SET @p_Dynamic_Sql = '
 RECONFIGURE WITH OVERRIDE 
 '
 EXEC master..sp_executesql @p_Dynamic_Sql
 ---------------
 SET @p_Dynamic_Sql = '
 DECLARE
@p_i int
 SELECT @p_i = MAX(' + @colname + ') FROM ' + @tablename + '
 DBCC CHECKIDENT (' + @tablename + ', RESEED, @p_i)
 '
 EXEC ( @p_Dynamic_Sql )
 RETURN ( 0 )
GO
GRANT EXECUTE ON Renum_Identity_Column TO PUBLIC
GO 
----------------------------------------
----------