Set the correct identity value for each table

When breaking replication we need to set the correct identity values for every table. This cursor checks all tables for identity columns and reseeds the identity to the correct next value. Without this we will have problems with duplicate values.

SET NOCOUNT ON
DECLARE @message Nvarchar(80)
DECLARE Ident CURSOR FOR
SELECT 'dbcc CHECKIDENT ('+table_name +',reseed)'
FROM information_schema.tables
WHERE objectproperty(object_id(table_name), 'IsUserTable') = 1
AND objectproperty(object_id(table_name), 'IsMSShipped') = 0
AND ident_current(table_name) IS NOT NULL
OPEN Ident
FETCH NEXT FROM Ident
INTO @message
WHILE @@FETCH_STATUS = 0
BEGIN
EXECUTE sp_executesql @message
FETCH NEXT FROM Ident INTO @message
END
CLOSE Ident
DEALLOCATE Ident