how reseed selected basing on last count.i have written query to reseed basing on last count.but how 10 tables @ time .
declare @last int select @last=max(empid) table_1 dbcc checkident (table_1, reseed, @last) but how more 10 tables or more tables...reseeding @ 1 go basing on last count
iterate through tables want , run above commands per table.
you'll have build sql statements dynamically.
for example, , sql server 2008 r2 (you don't specify using), trick:
declare @tname sysname, -- hold each table name @sname sysname, -- hold each table's schema name @idcol sysname, -- hold name of identity column of each table @sql nvarchar(4000) -- build each dynamic sql statement -- declare cursor iterate through table , schema names -- of current database. -- add clause here if needed. declare idtables cursor select name, schema_name(schema_id) sys.tables open idtables -- fetch first table , schema name corresponding variables fetch next idtables @tname, @sname while @@fetch_status = 0 begin -- ensure no dirty values if table has no identity column set @idcol = null -- build 1st statement. -- objective: identity column name, if any. set @sql = 'select @idcolname = name sys.columns object_id = object_id(''' + @sname + '.' + @tname + ''') , is_identity = 1' -- run statement , store result @idcol exec sp_executesql @sql, n'@idcolname sysname output', @idcolname = @idcol output if @idcol not null begin -- time 2nd statement. -- objective: find maximum identity value , reseed table. set @sql = 'declare @lastid int; select @lastid = max(' + @idcol + ') [' + @sname + '].[' + @tname + ']; if @lastid not null dbcc checkident (''' + @sname + '.' + @tname + ''', reseed, @lastid)' exec sp_executesql @sql end fetch next idtables @tname, @sname end close idtables deallocate idtables 
Comments
Post a Comment