CODEWITHSUNDEEP
sqlsql-serverdatabaset-sqltriggers
Igor Voplov
Igor Voplov

Reputation: 983

List of triggers for each table?

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.

Upvotes: 1

Views: 369

Answers (2)

Devart
Devart

Reputation: 121912

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'

Upvotes: 2

Rohit
Rohit

Reputation: 10236

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

Upvotes: 1

Related Questions