Skip to content
Amal Hashim
All posts
Snippet

SQL List All Triggers In Database

A query that returns every trigger in a database with its table, type and whether it is disabled.

1 min read#SQL

This query returns all triggers in a database, along with the table each one belongs to and its properties.

sql
USE YourDBName
GO
SELECT trigger_name = sysobjects.name,
      trigger_owner = 'x',
      table_schema = s.name,
      table_name = OBJECT_NAME(parent_obj),
      isupdate = OBJECTPROPERTY( id, 'ExecIsUpdateTrigger'),
      isdelete = OBJECTPROPERTY( id, 'ExecIsDeleteTrigger'),
      isinsert = OBJECTPROPERTY( id, 'ExecIsInsertTrigger'),
      isafter = OBJECTPROPERTY( id, 'ExecIsAfterTrigger'),
      isinsteadof = OBJECTPROPERTY( id, 'ExecIsInsteadOfTrigger'),
      [disabled] = OBJECTPROPERTY(id, 'ExecIsTriggerDisabled')
   FROM sysobjects
   INNER JOIN sys.tables t ON sysobjects.parent_obj = t.object_id
   INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
   WHERE sysobjects.type = 'TR'

Each flag column returns 1 when the trigger has that property and 0 when it does not.