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
