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

sql sql-server sql-server-2008-r2 replication identity-column
Zeeshanef picture Zeeshanef · Dec 19, 2014 · Viewed 13.6k times · Source

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

Brandon Williams picture Brandon Williams · Dec 19, 2014

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'

Related questions

Script to kill all connections to a database (More than RESTRICTED_USER ROLLBACK)

I have a development database that re-deploy frequently from a Visual Studio Database project (via a TFS Auto Build). Sometimes when I run my build I get this error: ALTER DATABASE failed because a lock could not be placed on …

Read More
Select SQL Server database size

how can i query my sql server to only get the size of database? I used this : use "MY_DB" exec sp_spaceused I got this : database_name database_size unallocated space My_DB 17899.13 MB 5309.39 MB It returns me several …

Read More
CREATE TABLE IF NOT EXISTS equivalent in SQL Server

CREATE TABLE IF NOT EXISTS works on mysql but fails with SQL Server 2008 R2. What is the equivalent syntax?

Read More