2

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.

  • 1
    What error or incorrect result are you getting ? – Conrad S. May 21 at 20:50
4
  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
    
  • YOU ARE THE BEST! Works great. Truncating wouldnt work as we often only delete a subset of values. If im being honest, I just like when there are no gaps in the LU values. Its just for looks. thank You! – DBA_Kyle May 21 at 21:04
0

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)'

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.