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;

Answer 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'