Find all Triggers

I was asked to find all tables in a database that had a DML trigger so a developer could ensure that all the old functionality was available in the new database. So once I built my query to give me the information, I thought I'd save it here for future use.

select a.name as 'Trigger', b.name as 'Table'
from sysobjects a
join sysobjects b
on a.parent_obj=b.id
where a.xtype = 'TR'

This gave me a nice little result set of each trigger and what table it was associated. Maybe I won't have to re-invent the wheel the next time they ask.

Find locked SQL accounts on SQL 2005: 1 year ago
Stored Procedure with Execute as: 1 year and 3 months ago
Cleaning up the disk: 1 year and 4 months ago
Restoring Last Full Backup: 3 days ago
DBCC Checkdb: 4 months and 9 days ago

Categories: MSSQL (RSS)
Tags: Database (RSS), SQL Server (RSS), t-sql (RSS)

Share this post

1 comment

#1. SQLRockstar
1 year ago

funny, i just put together some t-sql for finding triggers as well:

SELECT name, OBJECT_NAME(parent_id), is_disabled as [Parent Object] FROM sys.triggers

i use sys.triggers because it includes DDL triggers, which are not in the sysobjects table.

Write a comment

If you want to add your comment on this post, simply fill out the next form:

* Required fields

You can use these XHTML tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>.

No trackbacks

To notify a mention on this post in your blog, enable automated notification (Options > Discussion in WordPress) or specify this trackback url: http://www.deputyfamily.net/MichaelDeputy/archive/2009/08/find-all-triggers/trackback/

Latest comments

Who’s Winning the Olympics?:
- MichaelDeputy: That is a very astute observation, but if one athlete were able to win 2 golds and 2 silvers it would give the country a huge boost and obviously that country produced the best athlete (in his/her discipline) which is what I am looking for. After all the song they play is the national...
- SQLRockstar: how about tracking the distinct number of athletes that win medals? if one country sends ten athletes, but one athlete wins two golds and two silvers, then their score (by your scale) would be a 1.0, which is quite skewed. it would be better to only count that athlete once, not sure which medal...
- Jenny: Very cool system. I can't wait to see how your scores pan out over the next two weeks.
SKU is Invalid when installing 2nd node in SQL 2008:
- Vittorio Pavesi: Thanks for reference ;-)
Find all Triggers:
- SQLRockstar: funny, i just put together some t-sql for finding triggers as well: SELECT name, OBJECT_NAME(parent_id), is_disabled as [Parent Object] FROM sys.triggers i use sys.triggers because it includes DDL triggers, which are not in the sysobjects table.

SQLLawman