Reinitializing an identity column is of two folds. If there is no foregin key on the table where we want to reset the identity to 0 then the following process would work.
create table testTable1
(
sno int identity,
lastname varchar(25)
)
insert into testTable1 values('lastname1')
truncate table testTable1 -- This statement alone would do the trick for us :)
If there is a foreign key relationship then we can't use the above statement instead follow the steps explained below:
create table testTable1 -- example table 1
(
sno int identity primary key,
lastname varchar(25)
)
create table testTable2 -- example table 2
(
sno int references testTable1(sno),
email varchar(50)
)
insert into testTable1 values('lastname1')
insert into testTable2 values(1,'vmvadivel@yahoo.com')
delete testTable2 -- remove all the records from the child table first
delete testTable1-- remove all the records from the parent table
After deleting the content in the table(s) you need to give the execute below statement.
dbcc checkident(testTable1,Reseed,0)
There are cases where we want to reinitialize ALL identity columns in a database. In such cases make use of the below script.
Select
'dbcc checkident (' + sysobjects.name + ', Reseed, 0)' as 'Reset Identity for the whole database'
From
sysobjects, syscolumns
Where
sysobjects.id = syscolumns.id and
syscolumns.colstat & 1 <> 0 and
sysobjects.xtype = 'U' and
sysobjects.name <> N'dtproperties'
The result of the above query would be 'n' number of sql statements. Just copy those statements and execute it to reinitialize ALL identity columns in a database.
create table testTable1
(
sno int identity,
lastname varchar(25)
)
insert into testTable1 values('lastname1')
truncate table testTable1 -- This statement alone would do the trick for us :)
If there is a foreign key relationship then we can't use the above statement instead follow the steps explained below:
create table testTable1 -- example table 1
(
sno int identity primary key,
lastname varchar(25)
)
create table testTable2 -- example table 2
(
sno int references testTable1(sno),
email varchar(50)
)
insert into testTable1 values('lastname1')
insert into testTable2 values(1,'vmvadivel@yahoo.com')
delete testTable2 -- remove all the records from the child table first
delete testTable1-- remove all the records from the parent table
After deleting the content in the table(s) you need to give the execute below statement.
dbcc checkident(testTable1,Reseed,0)
There are cases where we want to reinitialize ALL identity columns in a database. In such cases make use of the below script.
Select
'dbcc checkident (' + sysobjects.name + ', Reseed, 0)' as 'Reset Identity for the whole database'
From
sysobjects, syscolumns
Where
sysobjects.id = syscolumns.id and
syscolumns.colstat & 1 <> 0 and
sysobjects.xtype = 'U' and
sysobjects.name <> N'dtproperties'
The result of the above query would be 'n' number of sql statements. Just copy those statements and execute it to reinitialize ALL identity columns in a database.
Comments