RSS

reset the identity coulmn

30 Nov

if you want to reset the identity coulmn in any table use The following line resets the Identity value for the table to 0 so that the next record added starts at 1
DBCC CHECKIDENT(‘Table Name’, RESEED, 0)
So i make Curssor to Select all tables with Identity column in any database then make reset to this tables

DECLARE @Table_Name AS NVARCHAR(100)
DECLARE @NewLineChar AS CHAR(2)
SET @NewLineChar = CHAR(13) + CHAR(10)
DECLARE Reset_Identity CURSOR
for
select o.name
from syscolumns c, sysobjects o
where c.status = 128  and c.xtype = 56 and c.colid =1 and c.offset = 2
and o.id = c.id
order by o.name
OPEN Reset_Identity

FETCH NEXT FROM Reset_Identity INTO @Table_Name
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
DECLARE @message varchar(100)
SELECT @message =  ‘DBCC CHECKIDENT(”’ + @Table_Name + ”’, RESEED, 0) ‘  + @NewLineChar +’GO’
PRINT @message
END
FETCH NEXT FROM Reset_Identity INTO @Table_Name
END

CLOSE Reset_Identity
DEALLOCATE Reset_Identity
GO

After you run this code on any database take the result becouse this code will genrate the reset identity column code in the database so if you take the result an run it in any database in this case you make reset  to the identity column

 
1 Comment

Posted by on November 30, 2011 in Script

 

One response to “reset the identity coulmn

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s