SQL Server – How to Reseed All Identity Columns

ddlidentitysql serversql-server-2008sql-server-2008-r2

I deleted all rows from 20 tables after disabling the constraints, triggers using the stored proc SP_MsForEachTable. Now before the import happens, I need to reseed all the identity columns back to start auto numbering from 1.

So I created a stored procedure that pass in the table name as a parameter, since it seems this will take time as its doing it individually, is there another way to do this for all of the tables at once?

   CREATE PROCEDURE [dbo].[USP_ReseedID]
@table_name varchar(50)
AS
BEGIN
    DBCC CHECKIDENT ( @table_name, RESEED, 0)

END

Best Answer

Instead of delete and then reseed, why not just truncate them? This deletes all the rows (in a slightly more efficient way than DELETE) AND reseeds the IDENTITY columns.

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += N'
  TRUNCATE TABLE ' + QUOTENAME(s.name) + '.' + QUOTENAME(t.name) + ';'
FROM sys.tables AS t
INNER JOIN sys.schemas AS s
ON t.[schema_id] = s.[schema_id]
WHERE EXISTS -- leave this out if you want to truncate ALL tables
(
  SELECT 1 
    FROM sys.identity_columns 
    WHERE [object_id] = t.[object_id]
);

PRINT @sql;
-- EXEC sp_executesql @sql;

You could generate a similar script, in a similar way, that applies the RESEED to each table, but why?

Also, stay away from awful stored procedures like sp_MSforeachtable. They're undocumented and unsupported for a reason. Check out the problems with sp_MSforeachdb:

(I haven't gotten around to proving it yet, but I suspect the procedure for tables - which has the same cursor and overall logic as for databases - can break in a similar way.)

Finally, if you can't truncate because of foreign keys, check out this script (#4):

https://www.sentryone.com/blog/aaronbertrand/grouped-concatenation-use-cases

Related Solutions

Sql-server – Moving tables to another SQL2008 database (including indexes, triggers, etc.)

Scripting out the tables, then using SSIS to transfer the data would be the most reliable and effective way of moving the data to the new database.

Sql-server – How to reset all of the identity values for a table, and reseed the auto-number in Sql Server 2008

The easiest way would be to start a new table. Inject the 30,000 rows you want to keep, then drop the old table and rename the new table.

BEGIN TRANSACTION;

CREATE TABLE dbo.copy_of_foo
(
  ID INT IDENTITY(1,1) PRIMARY KEY,
  col2 ..., ...
);

INSERT dbo.copy_of_foo(col2, ...) 
  SELECT TOP 30000 col2, ...
  FROM dbo.foo
  ORDER BY ...;

DROP TABLE dbo.foo;

EXEC sp_rename N'dbo.copy_of_foo', N'foo', N'OBJECT';

COMMIT TRANSACTION;

You may need to nest the order by and add OPTION (MAXDOP 1) if you want to ensure that you get the 30K most recent rows and the IDENTITY values are assigned in order from oldest to newest, e.g. hopefully you have some other column, such as a date time column, that can help identify the 30000 rows you want to keep:

INSERT dbo.copy_of_foo(col2, ...)
  SELECT col2, ...
  FROM 
  (
    SELECT TOP 30000 col2, ...
      FROM dbo.foo 
      ORDER BY date_time_column DESC
  ) AS x
  ORDER BY date_time_column
  OPTION (MAXDOP 1);

Keep in mind though that if someone reported an exception yesterday with row #42,000,564, good luck finding it today. As I suggested in the comment, maybe you shouldn't be exposing these obviously-otherwise-absolutely-meaningless surrogate identifiers to users.

Best Answer

Related Solutions

Sql-server – Moving tables to another SQL2008 database (including indexes, triggers, etc.)

Sql-server – How to reset all of the identity values for a table, and reseed the auto-number in Sql Server 2008

Related Question