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
    so.name TriggerName,
    USER_NAME(so.uid) TriggerOwner,
    USER_NAME(so2.uid) TableSchema,
    OBJECT_NAME(so.parent_obj) TableName,
    OBJECTPROPERTY( so.id, 'ExecIsUpdateTrigger') IsUpdate,
    OBJECTPROPERTY( so.id, 'ExecIsDeleteTrigger') IsDelete,
    OBJECTPROPERTY( so.id, 'ExecIsInsertTrigger') IsInsert,
    OBJECTPROPERTY( so.id, 'ExecIsAfterTrigger') IsAfter,
    OBJECTPROPERTY( so.id, 'ExecIsInsteadOfTrigger') IsInsteadOf,
    OBJECTPROPERTY(so.id, 'ExecIsTriggerDisabled') IsDisabled
FROM
       sysobjects so INNER JOIN sysobjects so2
        ON so.parent_obj = so2.Id
WHERE
       so.type = 'TR'


Pretty easy.  Your resultset will be something like this:



2 comments:

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

    ReplyDelete