We help IT Professionals succeed at work.

Reseed All tables in db using DBCC CHECKIDENT in one command

pazzaTim
pazzaTim used Ask the Experts™
on
We have a couple of databases that we are going to want to reseed the identity fields on about 100 tables to their current high record.

The reason for this is that we want to move a peer database to the "master" and the identity fields have not been incremented properly.

Is there a means of doing this for all tables in a database instead of doing each individual table one at a time?

Thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2011

Commented:
no you have to generate the 100 dbcc commands... and then run them...

consider using sp_msforeachtable or a select from information_schema.tables
to generate you list of commands....

what values were (where are they stored) you going to use for the new reseed value anyway?
C# ASP.NET Developer
Top Expert 2010
Commented:
The code is below. Just uncomment the exec (and comment out the print, if desired).

set nocount on

declare @tables table (
  id int identity,
  tablename sysname,
  columnName sysname
)

declare @counter int
declare @count int
declare @newseed nvarchar(20)
declare @tablename sysname
declare @columnname sysname
declare @sql varchar(max)
declare @sqlex varchar(max)
declare @sqlMax nvarchar(max)
declare @sqlMaxEx nvarchar(max)
DECLARE @pvalue     VARCHAR(250)
DECLARE @param      NVARCHAR(250)

set @sql = 'DBCC CHECKIDENT (''@table'', RESEED, @value)'
set @sqlMax = 'SELECT @value=MAX(@pkfield) FROM @table'
SET @param = '@value VARCHAR(250) OUTPUT'
 
insert into @tables
select DISTINCT TABLE_NAME, COLUMN_NAME
from INFORMATION_SCHEMA.COLUMNS
where TABLE_SCHEMA = 'dbo'
and COLUMNPROPERTY(object_id(TABLE_NAME), COLUMN_NAME, 'IsIdentity') = 1
order by TABLE_NAME
select @count = @@ROWCOUNT

set @counter = 1
while @counter <= @count begin
  select @tablename = tablename, @columnname = columnName
    from @tables
    where id = @counter
  set @sqlMaxEx = REPLACE(REPLACE(@sqlmax, '@table', @tablename), '@pkfield', @columnname)
  print @sqlmaxex
  
  EXECUTE SP_EXECUTESQL @sqlMaxEx, @param, @value = @pvalue OUTPUT
  
  select @newseed = ISNULL(cast(@pvalue as int), 0) + 1
  set @sqlex = REPLACE(REPLACE(@sql, '@table', @tablename ), '@value', @newseed)
  print @sqlex
  --exec (@sqlex)
  set @counter = @counter + 1
end

Open in new window

Author

Commented:
amenkes:   Looks great...  I executed it and it doesn't appear anything happened.   I thought the it would print out what it would do if I uncommented the exec.  Do I need to set the DB name anywhere?

Thanks

Author

Commented:
in under a second I got 'Command(s) completed successfully.'

Author

Commented:
OK I think I've got it.

I put:

Use DatabaseName before the no count and it looks to be printing out everything.

Great!  Exactly what I was looking for.

Author

Commented:
Just add:

USE DataBaseName to the top of the solution.  Works great!
Adam MenkesC# ASP.NET Developer
Top Expert 2010

Commented:
It should work on the active DB, otherwise, you can set it with the
USE <db>

It won't actually show anything until you add a new record, and it should have the new id.

Create a new database (I did one called Test2), created a table called Test1

Then, when you run the script above, and add a record to the table, it should be 101.



CREATE TABLE [dbo].[Test1](
	[myID] [int] IDENTITY(1,1) NOT NULL,
	[Somedate] [smalldatetime] NULL
) ON [PRIMARY]

GO



USE [Test2];
SET NOCOUNT ON;
SET XACT_ABORT ON;
GO

SET IDENTITY_INSERT [dbo].[Test1] ON;
BEGIN TRANSACTION;
INSERT INTO [dbo].[Test1]([myID], [Somedate])
SELECT 1, '20100704 17:21:00.000' UNION ALL
SELECT 3, '20100702 17:21:00.000' UNION ALL
SELECT 5, '20100630 17:21:00.000' UNION ALL
SELECT 7, '20100628 17:21:00.000' UNION ALL
SELECT 2, '20100215 00:00:00.000' UNION ALL
SELECT 8, '20100315 00:00:00.000' UNION ALL
SELECT 100, '20100415 00:00:00.000' UNION ALL
SELECT 80, '20100601 00:00:00.000'
COMMIT;
GO

SET IDENTITY_INSERT [dbo].[Test1] OFF;

Open in new window

Results.PNG

Author

Commented:
It was what I was looking for. I can see from the print statements and the DBCC command that it was straightening out the identity fields properly for what we were looking for.

Much thanks.
Top Expert 2011

Commented:
I'M NOT CLEAR WHY yiu are reseting the seed value to number of rows plus 1 in each table?

surely the correct could be independent of that, as you don't know what relationships are involved
between the tables....


only YOU can address what should be a safe value to RESEED to given we have no knowledge of how
your system is supposed to be working...
Adam MenkesC# ASP.NET Developer
Top Expert 2010

Commented:
It is not the # of rows plus one, it is the Max(identity field) value + 1, and checks if null, make it 0, add 1 to make it reseed 1.

If you look at the posted image above, it clearly shows 8 records input in non-sequential order and the last record (#9) is the new seed value.
Top Expert 2011

Commented:
ok max(col) +1

but all that does is give you the current max value still in that table not what has previously been used
and then DELETED....


your max value could be 3 but the next identity value could be 103 if rows 4-102 have been deleted in
that table.... if the column is used as an fk to some other table reseting to 4 coukd cause a problem
was the point i'm trying to make...



Author

Commented:
Understood, but in this case there really are no relationships between the tables.   This was what we were looking for.  The identities had gotten whacked due to a replication issue and this provided the best way to just reset them to the next available number.