Where can I find all the triggers in my database

[Ali Alhussein]

Hello, I have created a couple of triggers in many different tables. is there a way to run a comman in sql window to see a list of all the triggers that I created, or telling me which tables that triggers are located.

Thanks

Ali

[axel]

Hi,

In sysobjects you can find most of all objects
So if youwant o see for example the triggers on the statstables table try this query !!!

select otabl.name , otr.name
from sysobjects as otabl
inner join sysobjects as otr on otabl.id = otr.parent_obj
where otr.type = 'tr' and otabl.name = 'statstables'

I think it can help you

Best regards

Axel
------------
Ali Alhussein at 3/21/01 9:20:21 AM


Hello, I have created a couple of triggers in many different tables. is there a way to run a comman in sql window to see a list of all the triggers that I created, or telling me which tables that triggers are located.

Thanks

Ali

[Larry]

Ali, there's a couple of ways (that I know) to do this:

1) Query the system tables (not recommended by M$ though) like this:

select so.name, so2.name as [table]
from sysobjects so
join sysobjects so2 on so.parent_obj = so2.id
and so.xtype = 'tr'

2) Execute 'sp_helptrigger' for each table in the current database:

sp_msforeachtable "print '?' exec sp_helptrigger '?'"

Hope this helps.

- Larry

------------
Ali Alhussein at 3/21/01 9:20:21 AM


Hello, I have created a couple of triggers in many different tables. is there a way to run a comman in sql window to see a list of all the triggers that I created, or telling me which tables that triggers are located.

Thanks

Ali

[Ali Alhussein]

Thanks for your

------------
axel at 3/21/01 10:59:38 AM

Hi,

In sysobjects you can find most of all objects
So if youwant o see for example the triggers on the statstables table try this query !!!

select otabl.name , otr.name
from sysobjects as otabl
inner join sysobjects as otr on otabl.id = otr.parent_obj
where otr.type = 'tr' and otabl.name = 'statstables'

I think it can help you

Best regards

Axel
------------
Ali Alhussein at 3/21/01 9:20:21 AM


Hello, I have created a couple of triggers in many different tables. is there a way to run a comman in sql window to see a list of all the triggers that I created, or telling me which tables that triggers are located.

Thanks

Ali

[Jonathan Yang]

It works! But sp_msforeachtable is not documented in SQL 2000 OL book.

Larry how do you know this?


------------
Larry at 3/21/01 11:00:41 AM

Ali, there's a couple of ways (that I know) to do this:

1) Query the system tables (not recommended by M$ though) like this:

select so.name, so2.name as [table]
from sysobjects so
join sysobjects so2 on so.parent_obj = so2.id
and so.xtype = 'tr'

2) Execute 'sp_helptrigger' for each table in the current database:

sp_msforeachtable "print '?' exec sp_helptrigger '?'"

Hope this helps.

- Larry

------------
Ali Alhussein at 3/21/01 9:20:21 AM


Hello, I have created a couple of triggers in many different tables. is there a way to run a comman in sql window to see a list of all the triggers that I created, or telling me which tables that triggers are located.

Thanks

Ali