Tag Archives: trigger

How to: List All Triggers In The Database

Sometimes developers request for a list of triggers found in the database. Of course, the easiest way is to click on the table hierarchy and then click on triggers folder, then you will see the list of triggers for the table. What if there are 100 tables found in one database?

Use the following query to list all triggers in the particular database.

USE <Database_Name>
GO
SELECT 
     sysobjects.name AS trigger_name 
    , USER_NAME(sysobjects.uid) AS trigger_owner 
    , s.name AS table_schema 
    , OBJECT_NAME(parent_obj) AS table_name 
    , OBJECTPROPERTY( id, 'ExecIsUpdateTrigger') AS isupdate 
    , OBJECTPROPERTY( id, 'ExecIsDeleteTrigger') AS isdelete 
    , OBJECTPROPERTY( id, 'ExecIsInsertTrigger') AS isinsert 
    , OBJECTPROPERTY( id, 'ExecIsAfterTrigger') AS isafter 
    , OBJECTPROPERTY( id, 'ExecIsInsteadOfTrigger') AS isinsteadof 
    , OBJECTPROPERTY( id, 'ExecIsTriggerDisabled') AS [disabled] 
FROM sysobjects 

INNER JOIN sysusers 
    ON sysobjects.uid = sysusers.uid 

INNER JOIN sys.tables t 
    ON sysobjects.parent_obj = t.object_id 

INNER JOIN sys.schemas s 
    ON t.schema_id = s.schema_id 

WHERE sysobjects.type = 'TR'