List of all triggers in SQL Server Database

In the following lesson, you can learn how to list all triggers in SQL Server Database.

I had the same task recently and I used the following for SQL Server 2012 DB. Use the management studio and connect to the database you want to search. Then execute the following script for a list of all triggers in SQL Server database with table name and table’s schema.

SELECT
 [so].[name] AS [trigger_name],
 USER_NAME([so].[uid]) AS [trigger_owner],
 USER_NAME([so2].[uid]) AS [table_schema],
 OBJECT_NAME([so].[parent_obj]) AS [table_name],
 OBJECTPROPERTY( [so].[id], 'ExecIsUpdateTrigger') AS [isupdate],
 OBJECTPROPERTY( [so].[id], 'ExecIsDeleteTrigger') AS [isdelete],
 OBJECTPROPERTY( [so].[id], 'ExecIsInsertTrigger') AS [isinsert],
 OBJECTPROPERTY( [so].[id], 'ExecIsAfterTrigger') AS [isafter],
 OBJECTPROPERTY( [so].[id], 'ExecIsInsteadOfTrigger') AS [isinsteadof],
 OBJECTPROPERTY([so].[id], 'ExecIsTriggerDisabled') AS [disabled] 
FROM sysobjects AS [so]
INNER JOIN sysobjects AS so2 ON so.parent_obj = so2.Id
WHERE [so].[type] = 'TR'

For SQL 2000

SELECT 
     o.name AS trigger_name 
    ,'x' AS trigger_owner 
    /*USER_NAME(o.uid)*/ 
    ,s.name AS table_schema 
    ,OBJECT_NAME(o.parent_obj) AS table_name 
    ,OBJECTPROPERTY(o.id, 'ExecIsUpdateTrigger') AS isupdate 
    ,OBJECTPROPERTY(o.id, 'ExecIsDeleteTrigger') AS isdelete 
    ,OBJECTPROPERTY(o.id, 'ExecIsInsertTrigger') AS isinsert 
    ,OBJECTPROPERTY(o.id, 'ExecIsAfterTrigger') AS isafter 
    ,OBJECTPROPERTY(o.id, 'ExecIsInsteadOfTrigger') AS isinsteadof 
    ,OBJECTPROPERTY(o.id, 'ExecIsTriggerDisabled') AS [disabled] 
FROM sysobjects AS o 
/*
INNER JOIN sysusers 
    ON sysobjects.uid = sysusers.uid 
*/  
INNER JOIN sysobjects AS o2 
    ON o.parent_obj = o2.id 

INNER JOIN sysusers AS s 
    ON o2.uid = s.uid 

WHERE o.type = 'TR'

List of all triggers in SQL Server Database