Stored Procedure - Reseed Identity

Question

I have a bunch of LU tables that have values added and deleted all the time. Therefore, I figured writing a quick stored procedure would serve me well and then can call the stored procedure on another stored procedure that deletes values from the LU tables and then reseeds the ID column directly after.

Stored procedure code

Create Procedure DBA.ReseedIdentity

@TableName nvarchar(20) = NULL,
@IdentityColumn nvarchar(25) = NULL

AS BEGIN 
SET NOCOUNT ON 
SET XACT_ABORT ON 

    
IF (@TableName is null or @IdentityColumn is null)
    BEGIN
        RAISERROR('You Must Specify a Table AND the ID Column from that Table to Reseed the Identity!', 16,1)
        RETURN;
    END 

Declare @MaxID int
set @MaxID = (SELECT ISNULL(MAX(@IdentityColumn),0) FROM @TableName)
DBCC CHECKIDENT(@TableName, RESEED, @MaxID)


END

I wanted to know if there is any trick to be able to pass the @TableName parameter following the FROM clause without dynamically creating it. I am new to writing stored procedures, so please forgive me if the answer is obvious.

Any help is greatly appreciated.

Answer 1

1. If you are completely deleting all the values from the lookup table, have you considered just truncating them? That would save on TLOG activity and automatically reseed the values.

2. You can't substitute parameters like that in queries, at least not for object names. You would need to do this dynamically.

3. Please see an example for you below. I beefed up the sanity check for you for edge cases you may not have considered... hopefully the below is clear.

   CREATE PROCEDURE DBA.ReseedIdentity
   (
       @TableName SYSNAME
       , @IdentityColumn SYSNAME
   )
   AS 
   BEGIN
   
   /** Sanity Checking **/
   IF @TableName IS NULL OR @IdentityColumn IS NULL
   BEGIN
       ;THROW 50000, 'Must specify table name and Identity column name.', 1;
   END
   
   IF OBJECT_ID(@TableName) IS NULL
   BEGIN
       ;THROW 50000, 'Tablename does not exist.', 1;
   END
   
   IF NOT EXISTS (SELECT TOP (1) 1 FROM sys.columns AS C WHERE C.name = @IdentityColumn AND C.object_id = OBJECT_ID(@TableName))
   BEGIN
       ;THROW 50000, 'Identity column is not found on specified table.', 1;
   END
   
   /** Reset Identity
       Assumes Identity column is an INT
       **/
   DECLARE @SQLCommand NVARCHAR(4000)
   
   SET @SQLCommand = N'
       DECLARE @MaxID INT;
       SET @MaxID = COALESCE((SELECT MAX(' + QUOTENAME(@IdentityColumn) + ') FROM ' + QUOTENAME(@TableName) + '), 1);
   
       DBCC CHECKIDENT(' + QUOTENAME(@TableName, '''') + ', RESEED, @MaxID);
       ';
   
   EXEC sp_executesql @SQLCommand;
   
   END
   

Answer 2

Another option which is very short coding. You could be able to use sp_MSforeachtable to loops tables and reseed identity value.

-- reseed the identity columns of each table back to their starting values
EXEC sys.sp_MSforeachtable  @command1 = 'RAISERROR(''Re-Seeding identity columns in ?.'', 0, 1);',
                            @command2 = 'SET IDENTITY_INSERT ? OFF;
                                            DECLARE @seed bigint;
                                            SELECT  @seed = CASE
                                                                WHEN last_value IS NULL THEN CAST(seed_value as bigint)
                                                                ELSE CAST(seed_value as bigint) - CAST(increment_value as bigint) 
                                                            END
                                            FROM    [sys].[identity_columns]
                                            WHERE   [object_id] = object_id(''?'');
                                            DBCC CHECKIDENT ( ''?'', RESEED, @seed);',
                            @whereand = 'AND EXISTS(SELECT TOP 1 1 FROM [sys].[columns] c WHERE c.object_id = o.id AND c.is_identity = 1)'