How to make a script that will change all triggers in a database to be NOT FOR REPLICATION? Is there a system procedure for it or we need to parse its definition and inject NOT FOR REPLICATION?
Yes, manually, but first I would try this way:
proc [dbo].[db_compare_make_trigger_NOT_FOR_REPLICATION_sp]
as
declare cur cursor fast_forward for
select st.name, definition, is_disabled, OBJECT_NAME(parent_id)
from sys.triggers st join
sys.sql_modules sm on st.object_id = sm.object_id
where is_ms_shipped = 0
and is_not_for_replication = 0
and parent_id > 0
declare @name nvarchar(127), @definition nvarchar(max), @is_disabled bit, @table nvarchar(127)
open cur
fetch next from cur into @name, @definition, @is_disabled, @table
while @@FETCH_STATUS = 0
begin
declare @sql nvarchar(max) = null
declare @name_bckp nvarchar(127) = '__' + @name + N'_' + replace(replace(replace(replace(CONVERT(nvarchar,getdate(), 126), '-', '_'), ':', '_'), '.', '_'), 'T', '_')
PRINT @NAME + ' ON ' + @table
set @sql = dbo.RegExReplace(@definition, 'AS\s+BEGIN', ' NOT FOR REPLICATION' + CHAR(13) + CHAR(10) + 'AS' + CHAR(13) + CHAR(10) + 'BEGIN')
if charindex('NOT FOR REPLICATION', @sql) = 0
set @sql = dbo.RegExReplace(@definition, 'AS\s+SET NOCOUNT ON', ' NOT FOR REPLICATION' + CHAR(13) + CHAR(10) + 'AS' + CHAR(13) + CHAR(10) + 'SET NOCOUNT ON')
if charindex('NOT FOR REPLICATION', @sql) > 0
begin try
--BCKP it
exec sys.sp_rename @NAME, @name_bckp
set @definition = 'DISABLE TRIGGER [' + @name_bckp + '] ON [' + @table + ']'
execute sp_executesql @definition
--create it
execute sp_executesql @sql
--set previous state
if @is_disabled = 1
set @definition = 'DISABLE TRIGGER [' + @NAME + '] ON [' + @table + ']'
else
set @definition = 'ENABLE TRIGGER [' + @NAME + '] ON [' + @table + ']'
execute sp_executesql @definition
set @definition = 'DROP TRIGGER [' + @name_bckp + ']'
execute sp_executesql @definition
print 'DONE!'
end try
begin catch
declare @msg nvarchar(4000) = ERROR_MESSAGE()
declare @esv int = error_severity()
declare @est int = error_state()
declare @lin int = error_line()
insert into db_log (dateCreated, msg, Level, State, Line, additional)
values(getdate(), @msg, @esv, @est, @lin, @NAME)
print 'ERROR check db_log: ' + @msg
end catch
fetch next from cur into @name, @definition, @is_disabled, @table
end
close cur
deallocate cur
And remained triggers I would do manually.
for this to work you will need: CLR Assembly RegEx Functions for SQL Server