Not for Replication Trigger | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Not for Replication Trigger

I have arround 20-25 triggers. I want to list the trigeers which have written not for Replication. Is there any SQL Script for that. —————————————-

I got it from the follwoing way
select case when CHARINDEX (‘not FOR REPLICATION’,text) > 0 then 1 else 0 end from sysobjects O ,syscomments C where O.xtype = ‘TR’ and O.Id = C.ID But I guess there shoudl be another way of doing it —————————————-

I checked BOL under OBJECTPROPERTY, but there doesn’t seem to be one specifically for triggers. There is one that is supposed to cover all object types, ‘IsReplProc’, but as we don’t have replication on any of our databases you’ll have to check in your environment if it says anything meaningful about triggers. Here’s something to get you started: declare @tr varchar(200), @id int declare test cursor
select [id], [name] from sysobjects where xtype = ‘tr’
open test
fetch next from test into @id, @tr
while @@fetch_status = 0
set @id = objectproperty(@id, ‘IsReplProc’)
if @id = 0 begin print @tr + ‘ not for replication!’ end
fetch next from test into @id, @tr
close test
deallocate test

And without Cursors select [name],
case when objectproperty(id, ‘IsReplProc’)=0 then ‘Not for Replication’ else ‘For Replication’ end as IsReplication
from sysobjects where xtype = ‘tr’
Madhivanan Failing to plan is Planning to fail
Thanks all for the help. —————————————-