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
Printable View
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
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
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
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
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