Geek Answers Handbook

Reseed sql server ids

I repeat the identity columns as follows:

EXEC sp_MSforeachtable "DBCC CHECKIDENT ( '?', RESEED, 0)"

The query fails if there is no identifier in the table. But I still need to re-set the identifier for all tables in the database with the identifier. (And do not throw an error if there is no identifier)

+4
source share
2 answers

Are all tables empty?

 DECLARE @sql NVARCHAR(MAX) = N''; SELECT @sql = @sql + N'DBCC CHECKIDENT(''' + QUOTENAME(OBJECT_SCHEMA_NAME([object_id])) + '.' + QUOTENAME(OBJECT_NAME([object_id])) + ''', RESEED, 0);' + CHAR(13) + CHAR(10) FROM sys.columns WHERE is_identity = 1; PRINT @sql; -- EXEC sp_executesql @sql;
+6
source

To move all tables to 0 that have an identity column on the same row:

 exec sp_MSforeachtable 'dbcc checkident(''?'', reseed, 0)', @whereand='and exists(select 1 from sys.columns c where c.object_id = o.id and is_identity = 1)'

Verification of identification information: the current identifier value is '33798', the current value of the column is '0'. DBCC execution completed. If DBCC printed error messages, contact your system administrator. Verification of identification information: the current identification value is "3359", the current value of the column is "0". DBCC execution completed. If DBCC printed error messages, contact your system administrator.

0
source

More articles:


All Articles