Question :
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?
Answer :
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';
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.