List all objects that have Trigger

This is very simple to retrieve this data from “sysobjects”. Note that this query returns DML triggers in database.

SELECT OBJECT_NAME(S.id) ObjectName ,
OBJECT_NAME(s.deltrig) TriggerName
FROM sysobjects S
WHERE 1 = 1
       AND S.xtype IN ( 'U', 'V' )
       AND S.deltrig > 0

UNION

SELECT OBJECT_NAME(S.id) ObjectName ,
OBJECT_NAME(s.instrig) TriggerName
FROM sysobjects S
WHERE 1 = 1
       AND S.xtype IN ( 'U', 'V' )
       AND S.instrig > 0

UNION

SELECT OBJECT_NAME(S.id) ObjectName ,
OBJECT_NAME(S.updtrig) TriggerName
FROM sysobjects S
WHERE 1 = 1
       AND S.xtype IN ( 'U', 'V' )
       AND S.updtrig > 0

UNION

SELECT OBJECT_NAME(S.id) ObjectName ,
OBJECT_NAME(S.seltrig) TriggerName
FROM sysobjects S
WHERE 1 = 1
       AND S.xtype IN ( 'U', 'V' )
       AND S.seltrig > 0
Advertisements

Leave a comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s