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. —————————————-
http://spaces.msn.com/members/dineshasanka

---

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 —————————————-
http://spaces.msn.com/members/dineshasanka

---

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
for
select [id], [name] from sysobjects where xtype = ‘tr’
open test
fetch next from test into @id, @tr
while @@fetch_status = 0
begin
set @id = objectproperty(@id, ‘IsReplProc’)
if @id = 0 begin print @tr + ‘ not for replication!’ end
fetch next from test into @id, @tr
end
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. —————————————-
http://spaces.msn.com/members/dineshasanka

---

]]>