You are here:Home » tsql » Reseed Identity for all tables in database

Reseed Identity for all tables in database

DECLARE @sql varchar(8000)
DECLARE @tbl VARCHAR(4000)
DECLARE @col VARCHAR(4000)
declare c_ident cursor FAST_FORWARD FOR
SELECT table_name,column_name
FROM
INFORMATION_SCHEMA.COLUMNS
WHERE
COLUMNPROPERTY -- get columns where is_identity = 1
(
OBJECT_ID(QUOTENAME(table_schema) + '.' + QUOTENAME(table_name)) -- table ID
,column_name
,'isidentity'
) = 1


OPEN c_ident
FETCH NEXT FROM c_ident INTO @tbl,@col
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL = '
DECLARE @RESEEDIDENT BIGINT
SET @RESEEDIDENT = (select ISNULL(max('+@col+'),0) from '+@tbl+')
DBCC CHECKIDENT ( '''+@tbl+''', RESEED, @RESEEDIDENT )

'
PRINT (@SQL)
--EXEC(@SQL)
FETCH NEXT FROM c_ident INTO @tbl,@col
END
CLOSE c_ident
DEALLOCATE c_ident