List of triggers for each table?

Question

How can I get a list of triggers associated with each table?

I’m trying to get something like this:

Table 1 – Trigger for Table 1 – Trigger for Table 1 – Trigger for Table 1

Table 2 – Trigger for Table 2 – Trigger for Table 2 – Trigger for Table 2

If doesnt have to be in this format as long as I can interpret the data easily.

You can find the answer here I think http://stackoverflow.com/questions/4305691/need-to-list-all-triggers-in-sql-server-database-with-table-name-and-tables-sch — satnhak, Apr 12 '13 at 08:34

Devart · Answer 1 · 2013-04-18T06:28:08.017

Try this one -

SELECT 
      object_name = s.name + '.' + o.name
    , trigger_name = t.name
    , o.type_desc
    , is_insert_trigger = OBJECTPROPERTY(t.[object_id], 'ExecIsInsertTrigger')
    , is_update_trigger = OBJECTPROPERTY(t.[object_id], 'ExecIsUpdateTrigger') 
    , is_delete_trigger = OBJECTPROPERTY(t.[object_id], 'ExecIsDeleteTrigger')
    , t.is_instead_of_trigger
    , t.is_disabled
FROM sys.objects o WITH (NOWAIT) 
JOIN sys.schemas s WITH (NOWAIT) ON o.[schema_id] = s.[schema_id]
JOIN sys.triggers t WITH (NOWAIT) ON o.[object_id] = t.parent_id
WHERE o.[type] = 'U'

score 1 · Accepted Answer · answered Apr 12 '13 at 08:36

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'

Hope this helps

List of triggers for each table?

2 Answers2