Remove [NOT FOR REPLICATION] from all Identity columns of Database tables

Question

I have a Database which is containing lot of tables with Identity columns set to [NOT FOR REPLICATION]. in SQL Server 2008 R2 Is there any way that I can remove this constraint from all tables from Management Studio or any Query thanks.

Create Table mytbl (
[EmpId] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,

I tried this but it is removing one by one.

ALTER TABLE dbo.tblAttendance ALTER COLUMN Id
         DROP NOT FOR REPLICATION;

Solution 1

Microsoft provides a system stored procedure to turn on and off the NOT FOR REPLICATION setting. The stored procedure is sys.sp_identitycolumnforreplication. We can use this system stored procedure along with sp_msforeachtable to remove the NOT FOR REPLICATION setting from all tables:

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

Solution 2

I found that sys.sp_identitycolumnforreplication didn't work in my Ent. SQL 2008R2 or SQL 2012 instances (wil test in 2016 next). This little snippet worked for me. Set @debug as you see fit, to either run it, or copy-paste-run.

set nocount on 
declare @loop int = 1, @debug bit = 1, @schema sysname, @table sysname, @column sysname, @status int, @sqlStatement nvarchar(1000)
declare repl_cursor cursor for 
SELECT 
      [schema] = object_schema_name([object_id])
    , [table] = object_name([object_id])
    , [column] = [name] 
    , IsIdNotForRepl = COLUMNPROPERTY([object_id], [name], 'IsIdNotForRepl')
    , [--script] = 'alter table [' + object_schema_name([object_id]) + '].[' + object_name([object_id]) + '] alter column [' + [name] + '] DROP NOT FOR REPLICATION'
FROM sys.columns
        WHERE 1=1
        and COLUMNPROPERTY([object_id], [name], 'IsIdNotForRepl') = 1
        and object_schema_name([object_id]) <> 'sys'
order by 1,2

open repl_cursor
fetch next from repl_cursor into @schema, @table, @column, @status, @sqlStatement 
WHILE @@FETCH_STATUS = 0
BEGIN
    print '--loop: ' + cast(@loop as varchar(3)) + '; table: [' + @schema + '].[' + @table + '].[' + @column + ']; status = ' + case when isnull(@status,3) = 3 then 'NULL' else cast(@status as varchar(2)) end 
    print @sqlStatement + char(10)

    if @debug = 0 exec(@sqlStatement)

    FETCH NEXT FROM repl_cursor into @schema, @table, @column, @status, @sqlStatement 
    set @loop +=1
END 

close repl_cursor
deallocate repl_cursor 
go

Comments

[Zeeshanef]

Thanks. In my case the above did not solve the problem,this command will remove replication from database tables if replicated database was copied and attached. I solved my problem by going in Management Studio >Replication>Local Publications node and uncheck the replication from my database.

[Ian]

Thanks - this removes NOT FOR REPLICATION on my local db, but does not work on the Azure db. Any idea how?

[kuklei]

this works for all versions of SQL server and should be the best answer. Thanks for the script Abelian

[Nick.McDermaid]

By 'Does not work' I guess you get an error about sp_msforeachtable? I suggest you generate a script that creates a list of the required EXEC sys.sp_identitycolumnforreplication @int, 0 statements.

[Nick.McDermaid]

Perhaps your compatibility level was too old?

[Nugeswale]

This is the best answer on this page