reseed sql server identity columns

Question

I reseed identity columns like this:

EXEC sp_MSforeachtable "DBCC CHECKIDENT ( '?', RESEED, 0)"

Query throws me an error if there's no identity in the table. But I still need to reseed identity for all tables in the database that have an identity. (And do not throw an error if there's no identity)

Answer 1

Are all the tables really empty?

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

SELECT @sql = @sql + N'DBCC CHECKIDENT(''' 
    + QUOTENAME(OBJECT_SCHEMA_NAME([object_id]))
    + '.' + QUOTENAME(OBJECT_NAME([object_id])) 
    + ''', RESEED, 0);' + CHAR(13) + CHAR(10)
    FROM sys.columns
    WHERE is_identity = 1;

PRINT @sql;
-- EXEC sp_executesql @sql;

Answer 2

To reseed all tables to 0 that have an identity column in one line:

exec sp_MSforeachtable 'dbcc checkident(''?'', reseed, 0)', @whereand='and exists(select 1 from sys.columns c where c.object_id = o.id and is_identity = 1)'

Checking identity information: current identity value '33798', current column value '0'. DBCC execution completed. If DBCC printed error messages, contact your system administrator. Checking identity information: current identity value '3359', current column value '0'. DBCC execution completed. If DBCC printed error messages, contact your system administrator.