Обновление ВСЕХ триггеров с помощью "НЕ ДЛЯ РЕПЛИКАЦИИ" в SQL Server 2012

Question

У нас есть 505 триггеров, которые нуждаются в NOT FOR REPLICATION флаг установлен. Найден запрос пользователя, который ищет "%AS%" и добавляет NOT FOR REPLICATION как раз перед этим

Проблема в том, что он попадает в 1-й экземпляр AS, который может быть комментарием (т.е. -- Trigger to prevent update of LastUpdated column) или имя триггера (т.е. trgLastUpdated).

Ниже то, что мы пытались. У кого-нибудь есть предложения по поиску правильных AS и как разместить NOT FOR REPLICATION как раз перед этим?

Основной запрос:

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

который имеет пример вывода в столбце FixingScript:

-- =============================================
-- 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

Попытался изменить это с неудачными результатами:

PATINDEX(''%AS%'',

в

PATINDEX(char(13)+''%AS%''

или же

PATINDEX(''%''+char(13)+''AS%''

или же

PATINDEX(''%''+char(13)+char(10)+''AS%''

Answer 1

Ты пробовал ''% AS%'' (пробел перед КАК)?

Вы пробовали поиск с учетом регистра?
PATINDEX(''%AS%'', [skipped], COLLATE SQL_Latin1_General_Cp1_CS_AS) или любой другой случай сопоставления?

Кстати, МНОГО спасибо за сценарий:).

Answer 2

В моем коде я использую "case" вместо PATINDEX

select
'
---------------------------------------------------- 
---------------------------------------------------- 
----------------------------------------------------
DROP TRIGGER [dbo].[' + name + ']
GO 
----------------------------------------------------
' + 
SUBSTRING([definition], 1, FirstAsPosition) + ' NOT FOR REPLICATION ' + 
SUBSTRING([definition], FirstAsPosition, len([definition]))  
+ '
GO
----------------------------------------------------
'
AS SQLTriggersScript
 from 
(
select 
    name,[definition],
    case when 
        (case when AsIndex1 = 0 then 1000000 else AsIndex1 end)  
            < 
         (case when AsIndex2 = 0 then 1000000 else AsIndex2 end)    
             then 
        AsIndex1
    else 
        AsIndex2
    end AS FirstAsPosition
  from 
    (
        select 
            obj.name, [definition], 
            CHARINDEX(' AS', [definition]) as AsIndex1, 
            CHARINDEX(CHAR(13)+CHAR(10)+'AS', [definition]) as AsIndex2 
        from sys.sql_modules m
        inner join sys.objects obj on obj.object_id=m.object_id 
        where obj.type ='TR' 
        AND OBJECTPROPERTY(obj.object_id, 'ExecIsTriggerNotForRepl') = 0
        AND OBJECTPROPERTY(obj.object_id, 'IsMSShipped') = 0
    ) as t
) as t

Answer 3

Я предлагаю решение с использованием объектов управления SQL Server (SMO):

// compile with: 
// /r:Microsoft.SqlServer.Smo.dll
// /r:Microsoft.SqlServer.ConnectionInfo.dll
// /r:Microsoft.SqlServer.Management.Sdk.Sfc.dll 

// using System;
// using Microsoft.SqlServer.Management.Smo;
// using Microsoft.SqlServer.Management.Common;

        void M(){

            string sqlServerLogin ="__" ;
            string password = "__";
            string remoteSvrName ="__" ;
            string dbName = "__";

            ServerConnection conn = new ServerConnection(remoteSvrName);
            conn.LoginSecure = false;
            conn.Login = sqlServerLogin;
            conn.Password = password;
            Server srv = new Server(conn);

            var db = srv.Databases[dbName];

            foreach (Table tab in db.Tables)
            {
                foreach (Trigger trig in tab.Triggers)
                {
                    trig.TextMode = false;
                    trig.NotForReplication = true;
                    trig.TextMode = true;
                    trig.Alter();

                    Console.WriteLine(trig.Name);
                }
            }
}