2

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?

2 Answers 2

4

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';
0
3

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';
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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