Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have two SQL Server instances that I manage. One is a SQL Server 2000 instance, and the other is 2005.

Some where, on these servers, I recall setting up a couple table triggers that were executed under certain conditions. I need to look-up these triggers as a point of reference for a new project, however, for the life of me I can not find them.

Is there some crafty SQL statement that I can execute that can iterate over all my databases and, subsequently, iterate over each table and output any triggers associated with the tables?

share|improve this question

2 Answers 2

up vote 4 down vote accepted

This will give you a list of tables that have triggers, and the name of the associated trigger:

SELECT t.name, tr.name 
FROM sys.triggers tr
    INNER JOIN sys.tables t ON tr.parent_id = t.object_id

This will give you the same list, with a column that shows the actual text of the trigger statement:

SELECT t.name, tr.name, m.definition 
FROM sys.triggers tr
    INNER JOIN sys.tables t ON tr.parent_id = t.object_id
    INNER JOIN sys.sql_modules m ON tr.object_id = m.object_id

As Aaron suggested, this would work for SQL Server 2005+ as well:

SELECT t.name, tr.name, OBJECT_DEFINITION(tr.object_id) AS TriggerText 
FROM sys.triggers tr
    INNER JOIN sys.tables t ON tr.parent_id = t.object_id

And per his excellent suggestion, the following works for SQL Server 2000+ (including SQL Server 2012):

SELECT [table] = t.name, 
    [trigger] = tr.name,
    c.text
FROM sysobjects AS t
    INNER JOIN sysobjects AS tr  ON t.id = tr.parent_obj
    INNER JOIN syscomments AS c  ON tr.id = c.id
WHERE tr.xtype = 'TR'
    AND t.xtype = 'U';
share|improve this answer
    
Thank you. This seems to work for SQL 2005, is there a variation that will work for SQL 2000? –  RLH Mar 7 '13 at 21:34
2  
+1 Or just OBJECT_DEFINITION(tr.[object_id]) instead of the extra join. –  Aaron Bertrand Mar 7 '13 at 21:34
    
Nice Aaron - I wasn't aware of that. Thanks! –  Max Vernon Mar 7 '13 at 21:35

For SQL Server 2000:

SELECT 
  [table] = t.name, 
  [trigger] = tr.name,
  c.text
FROM sysobjects AS t
INNER JOIN sysobjects AS tr
  ON t.id = tr.parent_obj
INNER JOIN syscomments AS c
  ON tr.id = c.id
WHERE tr.xtype = 'TR'
AND t.xtype = 'U';

Max feel free to incorporate this into your answer.

share|improve this answer
    
Bingo! And this one helped my find the triggers that I was looking for. I could have sworn I had scripted out that database too. They must have been using a DBA mind trick. >_> –  RLH Mar 7 '13 at 21:47
    
Very cool, Aaron. Thanks. I assume this works for any type of tr.xtype such as views, stored procs, etc? –  Max Vernon Mar 7 '13 at 21:50
    
@MaxVernon do you mean getting the text from syscomments, or do you mean joining back to sysobjects? –  Aaron Bertrand Mar 7 '13 at 22:42
    
I was thinking I could change 'TR' to 'V' and get the text of a view. But apparently not. At least not without more than just a surface look at it. –  Max Vernon Mar 7 '13 at 22:43
    
@MaxVernon well you wouldn't need a double join to sysobjects for views, since they're a top-level object. So it would just be sysobjects AS o join syscomments AS c ON o.id = c.id where o.xtype = 'V'; –  Aaron Bertrand Mar 8 '13 at 3:26

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.