Reset all Identites to 1

Question

i have written code for reset all table's Identity to 1 but in some tables identity does not reset to 1 otherwise and will reset to 0 or 2 or other numeric this is my code

    declare @Count int 
    declare @C int 
    declare @Str varchar(20)
    set @C=1
    set @Count=(select  COUNT(*) TABLE_NAME from INFORMATION_SCHEMA.TABLES )
    while @C<@Count
    BEGIN
        with Records AS(select row_number() over(order by TABLE_NAME) as 'row1', *  
                    from INFORMATION_SCHEMA.TABLES)
        select  @Str=   TABLE_NAME from records
        where row1=@C
        set @C=@C+1
        DBCC CHECKIDENT (@Str , reseed, 0)
    END

check here: [http://stackoverflow.com/questions/724668/sql-server-identity-column-values-start-at-0-instead-of-1][1] [1]: http://stackoverflow.com/questions/724668/sql-server-identity-column-values-start-at-0-instead-of-1 — , Jul 12 '12 at 20:31

score 0 · Answer 1 · edited May 23 '17 at 12:20

0

gbn. have said If no rows have been inserted to the table since it was created, or all rows have been removed by using the TRUNCATE TABLE statement, the first row inserted after you run DBCC CHECKIDENT uses new_reseed_value as the identity. Otherwise, the next row inserted uses new_reseed_value + the current increment value but we want to reset all identity to 1. anyone done it?

edited May 23 '17 at 12:20

Community

1
1

answered Jul 15 '12 at 06:39

Salah Sanjabian

745
4
10
16

score 0 · Answer 2 · answered Jul 15 '12 at 09:23

Assuming that all of your tables are in fact empty this should work for you. It loops through all tables with IDENTITY(1,1) defined and reseeds them except for those which have never had a row inserted or have been truncated and don't need the reseed.

DECLARE @Name NVARCHAR(1000)
DECLARE @C1 AS CURSOR;

SET @C1 = CURSOR FAST_FORWARD
FOR SELECT Quotename(Object_schema_name(object_id)) + '.' 
                 + Quotename(Object_name(object_id))
    FROM   sys.identity_columns
    WHERE  Objectpropertyex(object_id, 'IsUserTable') = 1
           AND seed_value = 1
           AND increment_value = 1
           AND last_value IS NOT NULL;

OPEN @C1;

FETCH NEXT FROM @C1 INTO @Name;

WHILE @@FETCH_STATUS = 0
  BEGIN
      DBCC CHECKIDENT (@Name, reseed, 0)

      FETCH NEXT FROM @C1 INTO @Name;
  END

Reset all Identites to 1

2 Answers2