List all the triggers of a database

I needed a list of triggers in my database with created date and last modified date. This query helped me.
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
,trig.create_date
,trig.modify_date
,OBJECTPROPERTY( id, 'ExecIsUpdateTrigger') AS onupdate
,OBJECTPROPERTY( id, 'ExecIsDeleteTrigger') AS ondelete
,OBJECTPROPERTY( id, 'ExecIsInsertTrigger') AS oninsert
,OBJECTPROPERTY( id, 'ExecIsAfterTrigger') AS onafter
,OBJECTPROPERTY( id, 'ExecIsInsteadOfTrigger') AS oninsteadof
,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

INNER JOIN sys.triggers trig
ON trig.object_id = sysobjects.id

WHERE sysobjects.type = 'TR'

No comments:

Post a Comment