clear markdown feedback
clear markdown feedback
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 3364508 fiddles created (36299 in the last week).

CREATE TABLE T1 (ID INT IDENTITY, FOO INT); CREATE TABLE T2 (NID INT IDENTITY, BAR INT); INSERT INTO T1 VALUES (1),(2),(3); INSERT INTO T2 VALUES (1),(2),(3),(4),(5);
8 rows affected
 hidden batch(es)


DECLARE @TABLE_CATALOG NVARCHAR(128), @TABLE_SCHEMA NVARCHAR(128), @TABLE_NAME NVARCHAR(128), @COLUMN_NAME NVARCHAR(128); DECLARE @SCRIPT NVARCHAR(MAX); SET @SCRIPT = ''; BEGIN TRY CREATE TABLE #SCRIPTS ( COMMANDS NVARCHAR(500) ); DECLARE CURIDENT CURSOR READ_ONLY LOCAL FAST_FORWARD FOR SELECT C.TABLE_CATALOG, C.TABLE_SCHEMA, C.TABLE_NAME, C.COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS C JOIN INFORMATION_SCHEMA.TABLES T ON C.TABLE_CATALOG = T.TABLE_CATALOG AND C.TABLE_SCHEMA = T.TABLE_SCHEMA AND C.TABLE_NAME = T.TABLE_NAME WHERE TABLE_TYPE = 'BASE TABLE' AND COLUMNPROPERTY(OBJECT_ID(C.TABLE_SCHEMA + '.' + C.TABLE_NAME), C.COLUMN_NAME, 'IsIdentity') = 1 --AND T.TABLE_CATALOG = 'YOUR_CATALOG' ORDER BY C.TABLE_NAME; OPEN CURIDENT; FETCH NEXT FROM CURIDENT INTO @TABLE_CATALOG, @TABLE_SCHEMA, @TABLE_NAME, @COLUMN_NAME; WHILE @@FETCH_STATUS = 0 BEGIN SET @SCRIPT = N'DBCC CHECKIDENT(''' + (@TABLE_SCHEMA + N'.' + @TABLE_NAME) + N''', RESEED, ' + CAST(COALESCE(IDENT_CURRENT(@TABLE_SCHEMA + N'.' + @TABLE_NAME) + 1, 0) AS VARCHAR(30)) + ');' INSERT INTO #SCRIPTS VALUES (@SCRIPT); --UNCOMENT TO EXECUTE FOR EACH ROW --EXEC (@SCRIPT); FETCH NEXT FROM CURIDENT INTO @TABLE_CATALOG, @TABLE_SCHEMA, @TABLE_NAME, @COLUMN_NAME; END CLOSE CURIDENT; DEALLOCATE CURIDENT; SELECT * FROM #SCRIPTS; IF OBJECT_ID('tempdb..#SCRIPTS') IS NOT NULL DROP TABLE #SCRIPTS; END TRY BEGIN CATCH IF CURSOR_STATUS('LOCAL','CURIDENT') > -1 BEGIN CLOSE CURIDENT; DEALLOCATE CURIDENT; END IF OBJECT_ID('tempdb..#SCRIPTS') IS NOT NULL DROP TABLE #SCRIPTS; END CATCH
COMMANDS
DBCC CHECKIDENT('dbo.T1', RESEED, 4);
DBCC CHECKIDENT('dbo.T2', RESEED, 6);
 hidden batch(es)