Friday, May 4, 2018

Which SQL Server tables have triggers?

I performed a quick health check for a customer last night, and this was one of the performance-related findings:

      The [Insite] database has 16 triggers.

Not necessarily a problem, but it certainly could be. 😕  This is the query that I've modeled to go in and find the tables with any triggers, and to list the trigger type, and whether they are disabled... hopefully.

USE Insite;
SELECT TriggerName,
    USER_NAME(so.uid) TriggerOwner,
    USER_NAME(so2.uid) TableSchema,
    OBJECT_NAME(so.parent_obj) TableName,
    OBJECTPROPERTY(, 'ExecIsUpdateTrigger') IsUpdate,
    OBJECTPROPERTY(, 'ExecIsDeleteTrigger') IsDelete,
    OBJECTPROPERTY(, 'ExecIsInsertTrigger') IsInsert,
    OBJECTPROPERTY(, 'ExecIsAfterTrigger') IsAfter,
    OBJECTPROPERTY(, 'ExecIsInsteadOfTrigger') IsInsteadOf,
    OBJECTPROPERTY(, 'ExecIsTriggerDisabled') IsDisabled
       sysobjects so INNER JOIN sysobjects so2
        ON so.parent_obj = so2.Id
       so.type = 'TR'

Pretty easy.  Your resultset will be something like this:


  1. Not to be picky, but the first line shows 'lySE' instead of 'USE.' :-)