Search code examples
sqlsql-serverauto-increment

Reset AutoIncrement for all tables not starting with "_"

I need to reset the AutoIncrement field to 1 for tables in the database. I found that this can be done for a single table using:

DBCC CHECKIDENT (mytable, RESEED, 0)

How can I run this for all tables except tables with names that start with "_"?

Solution

  • I'm using the next script for to do that. Maybe using cursor is not very performing but it is not take much time.

    declare @TableName varchar(100)

declare cur_Cursor CURSOR STATIC
FOR 

  select  TABLE_NAME 
  from    information_schema.tables
  where   TABLE_TYPE = 'BASE TABLE'
    and   OBJECTPROPERTY(OBJECT_ID(TABLE_NAME), 'TableHasIdentity') = 1

OPEN  cur_Cursor
FETCH NEXT FROM cur_Cursor into @TableName
WHILE @@FETCH_STATUS = 0
BEGIN

  IF LEFT(@TableName, 1) != '_'
    DBCC CHECKIDENT(@TableName, RESEED, 0)

  FETCH NEXT FROM cur_Cursor into @TableName

END
CLOSE cur_Cursor
DEALLOCATE cur_Cursor