Sql-server – Reseed identity column in all empty tables

Since you can reset the IDENTITY by issuing a simple TRUNCATE:

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += N'TRUNCATE TABLE ' + QUOTENAME(s.name) + N'.'
  + QUOTENAME(t.name) + N';' + CHAR(13) + CHAR(10)
FROM sys.tables AS t
INNER JOIN sys.schemas AS s
ON t.[schema_id] = s.[schema_id]
INNER JOIN sys.identity_columns AS ic
ON t.[object_id] = ic.[object_id]
INNER JOIN sys.partitions AS p
ON p.[object_id] = t.[object_id]
WHERE p.[rows] = 0 -- only empty tables
AND p.index_id IN (0,1)
AND p.partition_number = 1;

PRINT @sql;
-- EXEC sys.sp_executesql @sql;

Though this will only work for tables not referenced by a foreign key. If you have foreign keys referencing these tables (likely), then you need:

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += N'DBCC CHECKIDENT(N''' + QUOTENAME(s.name) + N'.'
  + QUOTENAME(t.name) + N''', RESEED);' + CHAR(13) + CHAR(10)
FROM sys.tables AS t
INNER JOIN sys.schemas AS s
ON t.[schema_id] = s.[schema_id]
INNER JOIN sys.identity_columns AS ic
ON t.[object_id] = ic.[object_id]
INNER JOIN sys.partitions AS p
ON p.[object_id] = t.[object_id]
WHERE p.[rows] = 0 -- only empty tables
AND p.index_id IN (0,1)
AND p.partition_number = 1;

PRINT @sql;
-- EXEC sys.sp_executesql @sql;