Replication fun: setting NFR on all objects

If you ever tried to set up replication on the large environment with numerous servers, hundreds of databases then you, for sure, spend some time searching/writing scripts to set flag NOT FOR REPLICATION on thousands of identities, constraints and triggers. I want to share here my scripts and I hope it will save some readers hours of headache. Feel free to use them, just add comments below - there is nothing better than feedback.

Here we go.

********************************************************************************
1. Identities. The resutset is a list of identity columns on all tables in all databases where NOT FOR REPLICATION option is not set and fixing script:


DROP TABLE #IdentityNFR
CREATE TABLE #IdentityNFR (ServerName varchar(250), Dbname varchar(250),TableName varchar(250), IdentityColName varchar(250), IsNFR varchar(10),FixingScript varchar(4000))
exec sp_MSforeachdb '
IF ''?'' NOT IN (''tempdb'',''master'',''model'',''msdb'') BEGIN
USE ?
INSERT INTO #IdentityNFR
SELECT @@servername,''?'' DBName, OBJECT_NAME(c.object_id) TableName, c.name IdentityColumnName,
CASE ColumnProperty(object_id, name, ''IsIDNotForRepl'')
WHEN 0 THEN ''No''
WHEN 1 THEN ''Yes''
ELSE ''Unknown''
END [Is NFR],
''Alter Table [''+OBJECT_NAME(c.object_id)+''] Alter Column [''+c.name+''] ADD NOT FOR REPLICATION;'' FixingScript
FROM ?.sys.columns c
WHERE is_identity = 1
AND OBJECTPROPERTY(object_id, ''IsUserTable'') = 1
AND ColumnProperty(object_id, name, ''IsIDNotForRepl'') = 0
AND OBJECTPROPERTY(object_id, ''IsMSShipped'') = 0
END
'
select * from #IdentityNFR
********************************************************************************
********************************************************************************

2. Triggers. Same as above, the resutset is a list of all triggers on all tables in all databases where NOT FOR REPLICATION option is not set and fixing script:

DROP TABLE #TriggersNFR
CREATE TABLE #TriggersNFR (ServerName varchar(250), Dbname varchar(250),TableName varchar(250), TriggerName varchar(250), FixingScript varchar(max))
exec sp_MSforeachdb '
IF ''?'' NOT IN (''tempdb'',''master'',''model'',''msdb'') BEGIN
USE ?
insert into #TriggersNFR
SELECT @@servername,''?'' DBName,OBJECT_NAME(parent_object_id) TableName,
OBJECT_NAME(o.object_id) [Trigger Name],
STUFF (REPLACE(definition,''CREATE TRIGGER'',''ALTER TRIGGER''),
PATINDEX(''%AS%'',REPLACE(definition,''CREATE TRIGGER'',''ALTER TRIGGER'')),0,''
NOT FOR REPLICATION '')
+ ''
GO
''
from ?.sys.objects o
join ?.sys.sql_modules m
on o.object_id = m.object_id
where o.type = ''TR''
and OBJECTPROPERTY(o.object_id, ''ExecIsTriggerNotForRepl'') = 0
AND OBJECTPROPERTY(o.object_id, ''IsMSShipped'') = 0
END
'

select * from #TriggersNFR

********************************************************************************
********************************************************************************
3. Foreign keys.

This one is a little more tricky. In SQL 2005 we can use SQLDMO to get foreign key script, which is fantastic feature from my point of view. We do not need to think about how many columns we have in foreign key definition and no need to create complicated case structions. Unfortunately Microsoft decided to stop support for this feature in SQL 2008. So, pasting some simple script that will work with 1 column foreign key only, if you have foreign key on multiple column need do some fixing.
Just keep in mind that setting NOT FOR REPLICATION on Foreign keys ( as well as on check constraints) sets constraint as NOT TRUSTED which mean optimizer cannot trust this constraint any more.

********************************************************************************
SQL 2005:
SET NOCOUNT ON

DECLARE @rc int,@oServer int,@method varchar(300),@TSQL varchar(4000),@ScriptType int
DECLARE @DBname varchar(250),@TableName varchar(250),@FKName varchar(250)

IF OBJECT_ID('tempdb..#FKConstrNFR') IS NOT NULL DROP TABLE #FKConstrNFR
CREATE TABLE #FKConstrNFR ( Dbname varchar(250),TableName varchar(250), FKName varchar(250),Changed int)
exec sp_MSforeachdb '
IF ''?'' NOT IN (''tempdb'',''master'',''model'',''msdb'',''ReportServer'') BEGIN
USE ?
insert into #FKConstrNFR
SELECT ''?'' DBName,sh.name + ''.''+t.name TableName,
fk.name FKName,0
from ?.sys.foreign_keys fk
join ?.sys.tables t
on fk.parent_object_id = t.object_id
join ?.sys.schemas sh
on t.schema_id = sh.schema_id
WHERE is_not_for_replication = 0 and is_disabled = 0
AND OBJECTPROPERTY(fk.object_id, ''IsMSShipped'') = 0
END
'
EXEC sp_OACreate 'SQLDMO.SQLServer', @oServer OUT
EXEC sp_OASetProperty @oServer, 'loginsecure', 'true'
EXEC sp_OAMethod @oServer , 'Connect', NULL, @@servername, null , null
SET @ScriptType =1432262144

while exists ( select 1 from #FKConstrNFR
where Changed = 0 ) BEGIN

SELECT top 1 @DBname = Dbname,@TableName = TableName, @FKname = FKName
FROM #FKConstrNFR
WHERE Changed = 0

UPDATE #FKConstrNFR
SET Changed = 1
WHERE Dbname=@DBname and TableName=@TableName and FKName=@FKname

PRINT 'USE ' + @DBname
PRINT 'GO'

SET @TSQL = ''

SET @method = 'Databases("'+@DBname+'").Tables("'+@TableName+'").Keys("'+@FKname+'").Script' +
'(' + CAST (@ScriptType AS CHAR) + ')'

EXEC @rc=sp_OAMethod @oServer, @method , @TSQL OUTPUT

IF PATINDEX('%CASCADE%',@TSQL) > 0 BEGIN
SET @TSQL = STUFF(@TSQL,CHARINDEX('CASCADE',@TSQL,PATINDEX('%CASCADE%',@TSQL)+1)+7,0,' NOT FOR REPLICATION')
END
ELSE BEGIN
SET @TSQL = stuff(@TSQL,charindex(')',@TSQL,patindex('%REFERENCES%',@TSQL)),1,') NOT FOR REPLICATION')
END

PRINT @TSQL
--EXEC sp_OAGetErrorInfo @oServer

END

EXEC sp_OADestroy @oServer


********************************************************************************
SQL 2008:
set nocount on;

select 'BEGIN TRAN
ALTER TABLE '+ s.name + '.'+t.name+ ' DROP CONSTRAINT '+k.name+';
ALTER TABLE '+ s.name + '.'+ t.name+ ' WITH CHECK ADD CONSTRAINT '+k.name +
' FOREIGN KEY('+COL_NAME(c.parent_object_id,parent_column_id)+') REFERENCES '+
s2.name + '.' + t2.name+'('+COL_NAME(c.referenced_object_id,c.referenced_column_id)+') ' +

case when k.delete_referential_action = 1 then ' ON DELETE CASCADE ' ELSE '' END +
case when k.update_referential_action = 1 then ' ON UPDATE CASCADE ' ELSE '' END +
' NOT FOR REPLICATION
IF @@ROWCOUNT = 0 COMMIT TRAN ELSE ROLLBACK'
from sys.foreign_keys k
join sys.foreign_key_columns c
on k.object_id = c.constraint_object_id
join sys.tables t
on k.parent_object_id = t.object_id
join sys.schemas s
on t.schema_id = s.schema_id
join sys.tables t2
on c.referenced_object_id = t2.object_id
join sys.schemas s2
on t2.schema_id = s2.schema_id

where is_not_for_replication = 0

********************************************************************************
********************************************************************************