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
This entry was posted in Joy of T-SQL, T-SQL Tips & Tricks. Bookmark the permalink.

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s