find all triggers

How do I query the schema views (preferably) or sys tables to find all
triggers on my db?
tia
chrisDisplays the name of the table and the triggers:
Select c.name,a.name
From sysobjects a, sysdepends b, sysobjects c
Where a.id = b.id
And b.depid = c.id and a.type = 'TR' and c.type = 'U'
'U' defines tables, views are not incuded here, for that, leave out the last
part
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Chris" <Chris@.discussions.microsoft.com> schrieb im Newsbeitrag
news:BD6E22CA-A815-4CD1-AD15-590E6C168146@.microsoft.com...
> How do I query the schema views (preferably) or sys tables to find all
> triggers on my db?
> tia
> chris|||Select name TriggerName,
object_name(parent_obj) TableName
from sysobjects
Where type = 'TR'
"Chris" wrote:

> How do I query the schema views (preferably) or sys tables to find all
> triggers on my db?
> tia
> chris|||Hi Chris,
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:BD6E22CA-A815-4CD1-AD15-590E6C168146@.microsoft.com...
> How do I query the schema views (preferably) or sys tables to find all
> triggers on my db?
Try this:
select
trig.name as [Trigger Name],
tab.name as [Table Name],
case
when ObjectProperty( trig.id, 'ExecIsInsteadOfTrigger') = 1 then 'INSTEAD OF
' else 'FOR ' end
+ substring (
case when ObjectProperty( trig.id, 'ExecIsInsertTrigger') = 1 then ',
INSERT' else '' end +
case when ObjectProperty( trig.id, 'ExecIsUpdateTrigger') = 1 then ',
UPDATE' else '' end +
case when ObjectProperty( trig.id, 'ExecIsDeleteTrigger') = 1 then ',
DELETE' else '' end
, 3, 100) as [Trigger for]
from sysobjects as trig
inner join sysobjects as tab on tab.id = trig.parent_obj
where trig.type = 'TR'
order by tab.name
Or this:
select
object_name(parent_obj) as TableName,
name as TriggerName,
case(OBJECTPROPERTY(id, 'ExecIsTriggerDisabled'))
when 0 then 'YES'
when 1 then 'NO' end as Enable,
case(OBJECTPROPERTY(id, 'ExecIsInsertTrigger'))
when 0 then 'NO'
when 1 then 'YES' end as [Insert],
case(OBJECTPROPERTY(id, 'ExecIsUpdateTrigger'))
when 0 then 'NO'
when 1 then 'YES' end as [Update],
case(OBJECTPROPERTY(id, 'ExecIsDeleteTrigger'))
when 0 then 'NO'
when 1 then 'YES' end as [Delete],
case(OBJECTPROPERTY(id, 'ExecIsAfterTrigger'))
when 0 then 'Instead of'
when 1 then 'After' end as [Type],
crdate as CreationDate
from
sysobjects
where
type = 'TR'
order by
object_name(parent_obj), name

> tia
> chris
HTH,
Andrea - www.absistemi.it