Saturday 7 November 2015

[Solved] Remove Not For Replication for SQL Server Identity Columns

When i enabled replication for sql server db and its tables automatically all effected table create syntax changed and what i got that " Not For Replication " was added in all the identity columns of tables. I was stuck because had to update schema on production Sql server. I went through googling and what i found that there is a build in procedure available which removes all identity replication in one shot. I tried that and its work for me :)




This script turns it YES for all tables that have an identity column.

EXEC sp_msforeachtable @command1 = '
declare @int int
set @int =object_id("?")
EXEC sys.sp_identitycolumnforreplication @int, 1'

Given script turns it NO for all tables that have an identity column.

EXEC sp_msforeachtable @command1 = '
declare @int int
set @int =object_id("?")
EXEC sys.sp_identitycolumnforreplication @int, 0'