0

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  
  • How many times are you going to do this? Honestly? You know that even if you set these values back to starting at 1, IDENTITY columns do not guarantee contiguous sequences of values without gaps, right? – Aaron Bertrand Sep 10 '14 at 23:16
  • I knew that there is a better way to do this rather than doing it over and over again, but I didnt know that its not guaranteed the sequence values withouth gaps, can you share a link or something that gaps may be produced even after resetting them? or what do you suggest to solve this, thanks for your advice. – Shayma Ahmad Sep 10 '14 at 23:29
  • 1
    I don't know that there is a need to "solve" anything. Why do you care about gaps? These should be meaningless surrogate values that are not exposed to end users. Why would they need to be? Even if they are exposed, why should anybody care if there's a customer 6 and a customer 8 and no customer 7? If you don't want gaps, stop using IDENTITY. Very easy to reproduce a gap: INSERT; BEGIN TRANSACTION; INSERT; ROLLBACK; INSERT; SELECT; – Aaron Bertrand Sep 10 '14 at 23:45
  • 1
    Also, by "how many times are you going to do this?" I meant, how many times are you going to delete the data from all of your tables, not how many times are you going to call this stored procedure during this cleanup. – Aaron Bertrand Sep 10 '14 at 23:47
  • I probably need to do it only one time as they will import the same database from 3 different divisions to merge all in one database and one server, so most of these tables are similar but may have different records per facility. So we may get duplicate records that have the same identityID after the migration. You are right as its invisible to the end users. Thanks again for your comments, I will definitely check and read all the links you provided. – Shayma Ahmad Sep 11 '14 at 0:05
7

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):

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.