Technical Article

Reset Identity Columns for Tables Using Them

,

For all tables in the current database, it will script the DBCC command to reset the identity seed to 1 for all columns using them. Very handy for data migration.

SELECT 'DBCC CHECKIDENT (' + o.[name] + ', RESEED, 1)'
FROM syscolumns c
INNER JOIN sysobjects o ON o.[Id] = c.[Id]
INNER JOIN sysusers u ON o.[uid] = u.[uid]
WHERE c.[status] & 128 = 128
AND u.[name] = 'dbo'
AND o.[name] <> 'dtproperties'

Rate

4 (1)

You rated this post out of 5. Change rating

Share

Share

Rate

4 (1)

You rated this post out of 5. Change rating