title: Updating ALL Triggers with "NOT FOR REPLICATION" in SQL Server 2012
body: <p>We have 505 triggers that need the <code>NOT FOR REPLICATION</code> flag set. Found a user query that searches for '%AS%' and adds <code>NOT FOR REPLICATION</code> just before that.   </p>
  
  <p>The problem is it hits on the 1st instance of AS, which could be <em>comment</em> (i.e. <code>-- Trigger to prevent update of LastUpdated column</code>) or <em>trigger name</em> (i.e. <code>trgLastUpdated</code>).</p>
  
  <p>Below is what we have been trying. Does anyone have any suggestions for finding the correct <code>AS</code> and how to place <code>NOT FOR REPLICATION</code> just before it?  </p>
  
  <p><strong>Main query:</strong></p>
  
  <pre><code>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
  order by Dbname
  </code></pre>
  
  <p><strong>which has sample output in the FixingScript column:</strong></p>
  
  <pre><code>-- =============================================
  -- Author:      #####
  -- Create date: 9/14/2012
  -- Description: Prevents updating of a L
   NOT FOR REPLICATION astUpdated date that would cause app to drop an update when the next one comes along.
  -- =============================================
  ALTER TRIGGER trgAppUpdates_Upd_LastUpdated
     ON APP_REGISTER_UPDATES
     AFTER UPDATE
  AS 
  BEGIN
  </code></pre>
  
  <p>Have tried changing this with unsuccessful results:</p>
  
  <pre><code>PATINDEX(''%AS%'',
  </code></pre>
  
  <p>to</p>
  
  <pre><code>PATINDEX(char(13)+''%AS%''
  </code></pre>
  
  <p>or</p>
  
  <pre><code>PATINDEX(''%''+char(13)+''AS%''
  </code></pre>
  
  <p>or</p>
  
  <pre><code>PATINDEX(''%''+char(13)+char(10)+''AS%''
  </code></pre>