Add or Remove NOT FOR REPLICATION on All Identity Columns

When a replicated identity column is the source of trouble, you often need to toggle the NOT FOR REPLICATION attribute on every identity column in the database at once. This script does exactly that: flip @Action to ADD or DROP and it walks every non-system table's identity columns and issues the ALTER TABLE statement for each one.

/*
    ADD  = Adds "not for replication" attribute.
    DROP = Removes "not for replication" attribute.
*/
DECLARE @Action	varchar(50) SET @Action = 'ADD' --ADD/DROP
DECLARE @tSQL	 	varchar(max)
DECLARE @SchemaName	sysname
DECLARE @ObjectName	sysname
DECLARE @ColumnName	sysname

DECLARE NotForReplLoop CURSOR FAST_FORWARD FOR
    SELECT
	   SysS.name as SchemaName,
	   SysO.name as ObjectName,
	   SysC.name AS ColumnName
    FROM
	   sys.objects AS SysO
    INNER JOIN sys.schemas AS SysS
	   ON SysS.[schema_id] = SysO.[schema_id]
    INNER JOIN sys.columns AS SysC
	   ON SysC.[object_id] = SysO.[object_id]
    WHERE
	   SysO.is_ms_shipped = 0
	   AND COLUMNPROPERTY(SysO.[object_id], SysC.name, 'IsIdentity') = 1
	   AND COLUMNPROPERTY(SysO.[object_id], SysC.name, 'IsIdNotForRepl') = CASE @Action WHEN 'DROP' THEN 1 ELSE 0 END
OPEN NotForReplLoop

FETCH FROM NotForReplLoop INTO @SchemaName, @ObjectName, @ColumnName

WHILE(@@FETCH_STATUS = 0)
BEGIN--WHILE
    ------------------------------------------------------------------------------------------------------------

    PRINT 'Altering NOT FOR REPLICATION on [' + @SchemaName + '].[' + @ObjectName + '].[' + @ColumnName + ']'
    SET @tSQL = 'ALTER TABLE '
	   + '[' + @SchemaName + '].[' + @ObjectName + '] '
	   + 'ALTER COLUMN [' + @ColumnName + '] '
	   + @Action + ' NOT FOR REPLICATION;'
    EXEC(@tSQL)

    ------------------------------------------------------------------------------------------------------------
    FETCH FROM NotForReplLoop INTO @SchemaName, @ObjectName, @ColumnName
END--WHILE

CLOSE NotForReplLoop DEALLOCATE NotForReplLoop