Sql-server – way that I can export a list of all table triggers on a server

sql serversql-server-2000sql-server-2005trigger

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?

Best 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';
Related Question