ARY8MPRn

1Create Procedure DBA.ReseedIdentity
2
3@TableName nvarchar(20) = NULL,
4@IdentityColumn nvarchar(25) = NULL
5
6AS BEGIN 
7SET NOCOUNT ON 
8SET XACT_ABORT ON 
9
10
11IF (@TableName is null or @IdentityColumn is null)
12    BEGIN
13        RAISERROR('You Must Specify a Table AND the ID Column from that Table to Reseed the Identity!', 16,1)
14        RETURN;
15    END 
16
17Declare @MaxID int
18set @MaxID = (SELECT ISNULL(MAX(@IdentityColumn),0) FROM @TableName)
19DBCC CHECKIDENT(@TableName, RESEED, @MaxID)
20
21
22END 
23     
24/** Sanity Checking **/
25IF @TableName IS NULL OR @IdentityColumn IS NULL
26BEGIN
27    ;THROW 50000, 'Must specify table name and Identity column name.', 1;
28END
29
30IF OBJECT_ID(@TableName) IS NULL
31BEGIN
32    ;THROW 50000, 'Tablename does not exist.', 1;
33END
34
35IF NOT EXISTS (SELECT TOP (1) 1 FROM sys.columns AS C WHERE C.name = @IdentityColumn AND C.object_id = OBJECT_ID(@TableName))
36BEGIN
37    ;THROW 50000, 'Identity column is not found on specified table.', 1;
38END
39
40/** Reset Identity
41    Assumes Identity column is an INT
42    **/
43DECLARE @SQLCommand NVARCHAR(4000)
44
45SET @SQLCommand = N'
46    DECLARE @MaxID INT;
47    SET @MaxID = COALESCE((SELECT MAX(' + QUOTENAME(@IdentityColumn) + ') FROM ' + QUOTENAME(@TableName) + '), 1);
48
49    DBCC CHECKIDENT(' + QUOTENAME(@TableName, '''') + ', RESEED, @MaxID);
50    ';
51
52EXEC sp_executesql @SQLCommand;