How can I reseed all identity columns?

Question

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  

Answer 1

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:

• http://www.mssqltips.com/sqlservertip/2201/making-a-more-reliable-and-flexible-spmsforeachdb/

• https://sqlblog.org/2010/12/29/a-more-reliable-and-more-flexible-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