SQL Server – T-SQL script for automatic reseeding of all identity tables within a given database
After some back to back problem
driven posts, I thought to post something different, which should be equally tricky
but more to fun and what’s better than a good T-SQL script. Recently in one of
my projects I got a requirement to come up with a T-SQL script which can
automatically reseed all tables on any given database if need be.
Below is the result of this
requirement. I've created a SP which can automatically check for current
identity value of all the tables within a given database and can reseed it to
new value. The SP takes three parameter viz. Database Name "@db_name (nvarchar(255))", Print
Bit "@print_bit (bit)", Increment value "@incr_val (int)". This will reseed the value of identity column of
all the tables in a given database with new incremented value provided. The "@incr_val" defaults
to 0, which will reseed the identity column to start with immediate next value in the identity series defined. The "@print-bit" parameter gives
you the freedom and flexibility to choose to either to print all reseed
commands or to execute them directly.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--
=============================================
-- Author: Siddharth Chauhan
-- Description: This SP will reseed table with new incremented value for all
identity based tables within a given database
-- Usage: exec usp_AutoReseedDB @db_name = <database name>,
-- @print_bit
= {1|0} (1 - print, 0 - execute, Default is 1),
-- @incr_val = 0 (increment identity value
by this much. Default is 0)
--
=============================================
Create Procedure usp_AutoReseedDB @db_name
nvarchar(255), @print_bit bit=1, @incr_val int=1
as
begin
declare @scmd nvarchar(max)
select @scmd = N'use ' + @db_name +'
declare @ident_val1 int, @ident_val2
bigint, @cmd nvarchar(max), @print_bit bit, @incr_val int
declare @tab_name varchar(255), @col_name
varchar(255), @type_name varchar(255), @bit tinyint
select @bit = 0
select @print_bit = '+ convert(nchar(1),@print_bit) + '
select @incr_val = '+ convert(nvarchar(10),@incr_val) + '
declare cur_ident_tab cursor
fast_forward
for select object_name(object_id)
"table_name",name "column_name", type_name(system_type_id)
"Type_Name"
from sys.columns where is_identity = 1
open cur_ident_tab
print ''--starting cursor''
print '' ''
fetch next from cur_ident_tab into
@tab_name, @col_name, @type_name
while @@fetch_status = 0
begin
print
''--reseeding table: ''+ @tab_name
if
(@type_name = ''int'')
begin
select
@ident_val1 = ident_current(@tab_name)
set
@bit = 1
print
''--Current identity value: ''+convert(varchar(50),@ident_val1)
end
else
begin
select
@ident_val2 = ident_current(@tab_name)
set
@bit = 2
print
''--Current identity value: ''+convert(varchar(50),@ident_val2)
end
if
@bit = 1
begin
set
@ident_val1 = @ident_val1 + @incr_val
print
''--New identity value: ''+convert(varchar(50),@ident_val1)
select
@cmd = ''dbcc checkident('''''' + @tab_name + '''''', reseed, '' +
convert(nvarchar(50),@ident_val1) + '');''
set
@bit = 0
end
else
begin
if
@bit = 2
begin
set
@ident_val2 = @ident_val2 + @incr_val
print
''--New identity value: ''+convert(varchar(50),@ident_val2)
select
@cmd = ''dbcc checkident('''''' + @tab_name + '''''', reseed, '' +
convert(nvarchar(50),@ident_val2) + '');''
set
@bit = 0
end
end
if
@print_bit = 1
print
@cmd
else
exec
sp_executesql @cmd
fetch next from cur_ident_tab into
@tab_name, @col_name, @type_name
print '' ''
end
close cur_ident_tab
deallocate cur_ident_tab
print ''--end of cursor''
'
exec sp_executesql @scmd
end
As always please don't forget to share if you like and provide your valued feedback.
Comments
Post a Comment