CODEWITHSUNDEEP
t-sqlsql-server-2008-r2identity
Peter Hedberg
Peter Hedberg

Reputation: 3649

Reset any identity column to start with 1 as next value

I'm building a SQL script to reset a database. To do this I want to empty several (not all) tables and reset their identity values to 0.

For some tables I use TRUNCATE TABLE, but this does not work when the table has is referenced by a foreign key constraint. For those tables I use DBCC CHECKIDENT(TableName, RESEED, 0). This works for most tables, but not for all. If a table never had any data, the current identity value is NULL instead of 0:

CREATE TABLE TableName (Id int identity, Value varchar(10))
DBCC CHECKIDENT(TableName)

-- Checking identity information: current identity value 'NULL', current column value 'NULL'.

If I use DBCC CHECKIDENT(TableName, RESEED, 0) on them, they will have 0 as next identity value. I can instead use 1 as reseed value, but then the tables which have had data in them will start on 2.

Is it possible to find the next identity value in any of the sys-views? If I just could get next value into a variable, I could use it to set next value to either 0 or 1. I've tried with IDENT_CURRENT('TableName') but it says 1 for both NULL and 0 as next value.

And please, I don't want any questions like "why do you need all of them to start from 1" or any other question suggesting another result than the one I'm asking for.

Upvotes: 0

Views: 6830

Answers (3)

Lauren Glenn
Lauren Glenn

Reputation: 11

Disable the constraint first:

ALTER TABLE foo NOCHECK CONSTRAINT CK_foo_column

or

ALTER TABLE foo NOCHECK CONSTRAINT ALL

then do a truncate on the table

Upvotes: 0

Romil Kumar Jain
Romil Kumar Jain

Reputation: 20745

Use if all tables have identity field.

exec sp_MSforeachtable @command1 = 'DBCC CHECKIDENT(''?'', RESEED, 1)'

MSforeachtable is an undocumented, but extremely handy stored proc which executes a given command against all tables in your database.

To reseed ONLY tables with an identity column you can use the next script. It also makes use of sp_MSforeachtable but taking into account the correct tables.

EXEC sp_MSforeachtable '
IF (SELECT COUNT(1) 
    FROM INFORMATION_SCHEMA.TABLES 
    WHERE TABLE_TYPE = ''BASE TABLE'' 
    AND ''[''+ TABLE_SCHEMA + ''].['' + TABLE_NAME + '']'' = ''?'' 
    AND OBJECTPROPERTY(OBJECT_ID(TABLE_NAME), ''TableHasIdentity'') = 1) > 0 
BEGIN
    DBCC CHECKIDENT (''?'', RESEED, 1)
END'

Use Following query to get Last Identity value inserted in tables in a database.

   SELECT TableName = OBJECT_NAME(OBJECT_ID) ,
       ColumnName = name ,
       OriginalSeed = seed_value ,
       Step = increment_value ,
       LastValue = last_value ,
       IsNotForReplication = is_not_for_replication
FROM sys.identity_columns

Refer this for more details

Upvotes: 5

Cylindric
Cylindric

Reputation: 5894

To clarify, do you basically want all future inserts to begin at 1?

I'm no MS-SQL expert on this particular subject, but can you just truncate the table, then reset the identity as you've suggested, then if the identity is NULL, insert one record (which should get 0) then delete it? Would that not leave the next record to be created be 1?

Upvotes: 0

Related Questions