Remove [NOT FOR REPLICATION] from all Identity columns in database tables

Question

I have a database containing many tables with Identity columns set to [NOT FOR REPLICATION]. in SQL Server 2008 R2 Is there a way to remove this restriction from all tables from Management Studio or any query.

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

I tried this, but it is deleted one by one.

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

Answer 1

Microsoft provides a system stored procedure to enable or disable the NOT FOR REPLICATION setting. Sys.sp_identitycolumnforreplication stored procedure We can use this system stored procedure along with sp_msforeachtable to remove the NOT FOR REPLICATION parameter from all tables:

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

Answer 2

I found that sys.sp_identitycolumnforreplication does not work in my Ent. SQL 2008R2 or SQL 2012 (followed by testing in 2016). This little snippet worked for me. Install @debug as you wish to run it or copy-paste.

 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