Need to list all triggers in SQL Server database with table name and table s schema

Question

I need to list all triggers in SQL Server database with table name and table s schema   I m almost there with this   SELECT trigger name   name  trigger owner   USER NAME uid  table schema     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 sysusers ON sysobjects uid   sysusers uid WHERE type    TR    I just need to get the table s schema also

User · Answer

If you are looking for ALL triggers  remember MS-SQL has both SQL-based triggers  sysobjects type    TR   and CLR-based triggers  sysobjects type    TA

User · Answer

CREATE TABLE  dbo   VERSIONS            ID   uniqueidentifier  NOT NULL           DATE   varchar  100  NULL           SERVER   varchar  100  NULL           DATABASE   varchar  100  NULL           USER   varchar  100  NULL           OBJECT   varchar  100  NULL           ACTION   varchar  100  NULL           CODE   varchar  max  NULL       CONSTRAINT  PK VERSIONS  PRIMARY KEY CLUSTERED                 ID  ASC      WITH  PAD INDEX   OFF  STATISTICS NORECOMPUTE   OFF  IGNORE DUP KEY   OFF  ALLOW ROW LOCKS   ON  ALLOW PAGE LOCKS   ON  ON  PRIMARY        ON  PRIMARY  TEXTIMAGE ON  PRIMARY      GO      ALTER TABLE  dbo   VERSIONS  ADD  CONSTRAINT  DF VERSIONS ID   DEFAULT  newid    FOR  ID      GO       DROP TRIGGER  DB VERSIONS TRIGGER  ON ALL SERVER      CREATE TRIGGER  DB VERSIONS TRIGGER  ON ALL SERVER FOR CREATE PROCEDURE  ALTER PROCEDURE  DROP PROCEDURE       CREATE TRIGGER  ALTER TRIGGER  DROP TRIGGER  CREATE FUNCTION  ALTER FUNCTION  DROP FUNCTION  CREATE VIEW  ALTER VIEW       DROP VIEW  CREATE TABLE  ALTER TABLE  DROP TABLE      AS      SET NOCOUNT ON SET XACT ABORT OFF       BEGIN          TRY              DECLARE  DATA XML   EVENTDATA                DECLARE  SERVER VARCHAR 100     DATA value   EVENT INSTANCE ServerName  1    VARCHAR 100                 DECLARE  DATABASE VARCHAR 100     DATA value    EVENT INSTANCE DatabaseName  1     VARCHAR 100                 DECLARE  USER VARCHAR 100     DATA value    EVENT INSTANCE LoginName  1    VARCHAR 100                 DECLARE  OBJECT VARCHAR 100     DATA value   EVENT INSTANCE ObjectName  1    VARCHAR 100                 DECLARE  ACTION VARCHAR 100     DATA value    EVENT INSTANCE EventType  1    VARCHAR 100                 DECLARE  CODE VARCHAR MAX     DATA value    EVENT INSTANCE  TSQLCommand  1    VARCHAR MAX                   IF OBJECT ID  DB VERSIONS dbo VERSIONS   IS NOT NULL              BEGIN                  INSERT INTO  DB VERSIONS   dbo   VERSIONS   SERVER    DATABASE    USER    OBJECT    ACTION    DATE    CODE   VALUES   SERVER   DATABASE   USER   OBJECT   ACTION  getdate    ISNULL  CODE   NA                 END          END          TRY          BEGIN              CATCH          END      CATCH      RETURN

User · Answer

This is what I use  usually wrapped in something I stuff in Model    Select    Parent    Left  Case When Tr Parent Class   0 Then   Database   Else Object Name Tr Parent ID  End   32      Schema    Left Coalesce Object Schema Name Tr Object ID     None     16      Trigger name    Left Tr Name  32       Type    Left Tr Type Desc  3   -- SQL or CLR    MS      Case When Tr Is MS Shipped   1 Then  X  Else     End      On      Case When Tr Is Disabled   0 Then  X  Else     End      Repl      Case When Tr Is Not For Replication   0 Then  X  Else     End      Event    Left  Case When Tr Parent Class   0                         Then  Select Top 1 Left Te Event Group Type Desc  40                               From Sys Trigger Events As Te                              Where Te Object ID   Tr Object ID                         Else   Case When Tr Is Instead Of Trigger   1 Then  Instead Of   Else  After   End                                  SubString Cast  Select  text             Left Te Type Desc  1    Lower SubString Te Type Desc  2  32                                                          Case When Te Is First   1 Then    First   When Te Is Last   1 Then    Last   Else    End                                               From Sys Trigger Events As Te                                              Where Te Object ID   Tr Object ID                                              Order By Te  Type                                               For Xml Path       As Character Varying   3  60  End   60    -- If you like     --    Get text with     Select Object Definition     Cast Tr Object ID As Character Varying        From    Sys Triggers As Tr Order By   Tr Parent Class  -- database triggers first   Parent -- alphabetically by parent   As you see it is a skosh more McGyver  but I think it s worth it   Parent                           Schema           Trigger name                     Type MS   On   Repl  Event -------------------------------- ---------------- -------------------------------- ---- ---- ---- ----- -----------------------------------------  Database                         None            ddlDatabaseTriggerLog            SQL            X     DDL DATABASE LEVEL EVENTS Employee                         HumanResources   dEmployee                        SQL       X          Instead Of Delete Person                           Person           iuPerson                         SQL       X          After Insert  Update PurchaseOrderDetail              Purchasing       iPurchaseOrderDetail             SQL       X    X     After Insert PurchaseOrderDetail              Purchasing       uPurchaseOrderDetail             SQL       X    X     After Update PurchaseOrderHeader              Purchasing       uPurchaseOrderHeader             SQL       X    X     After Update SalesOrderDetail                 Sales            iduSalesOrderDetail              SQL       X    X     After Insert  Update  Delete SalesOrderHeader                 Sales            uSalesOrderHeader                SQL       X          After Update  First  Vendor                           Purchasing       dVendor                          SQL       X          Instead Of Delete WorkOrder                        Production       iWorkOrder                       SQL       X    X     After Insert WorkOrder                        Production       uWorkOrder                       SQL       X    X     After Update    Scroll right to see the final and most useful column

User · Answer

One difficulty is that the text  or description has line feeds  My clumsy kludge  to get it in something more tabular  is to add an HTML literal to the SELECT clause  copy and paste everything to notepad  save with an html extension  open in a browser  then copy and paste to a spreadsheet  example  SELECT obj NAME AS TBL trg name sm definition   lt br gt   FROM SYS OBJECTS obj LEFT JOIN  SELECT trg1 object id trg1 parent object id trg1 name FROM sys objects trg1 WHERE trg1 type  tr  AND trg1 name like  update    trg  ON obj object id trg parent object id LEFT JOIN  SELECT sm1 object id sm1 definition FROM sys sql modules sm1 where sm1 definition like   suser sname      sm ON trg object id sm object id WHERE obj type  u  ORDER BY obj name    you may still need to fool around with tabs to get the description into one field  but at least it ll be on one line  which I find very helpful

User · Answer

Here s one way   SELECT       sysobjects name AS trigger name       USER NAME sysobjects uid  AS trigger owner       s name AS table schema       OBJECT NAME parent obj  AS table name       OBJECTPROPERTY  id   ExecIsUpdateTrigger   AS isupdate       OBJECTPROPERTY  id   ExecIsDeleteTrigger   AS isdelete       OBJECTPROPERTY  id   ExecIsInsertTrigger   AS isinsert       OBJECTPROPERTY  id   ExecIsAfterTrigger   AS isafter       OBJECTPROPERTY  id   ExecIsInsteadOfTrigger   AS isinsteadof       OBJECTPROPERTY id   ExecIsTriggerDisabled   AS  disabled   FROM sysobjects   INNER JOIN sysusers      ON sysobjects uid   sysusers uid   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     EDIT  Commented out join to sysusers for query to work on AdventureWorks2008   SELECT       sysobjects name AS trigger name       USER NAME sysobjects uid  AS trigger owner       s name AS table schema       OBJECT NAME parent obj  AS table name       OBJECTPROPERTY  id   ExecIsUpdateTrigger   AS isupdate       OBJECTPROPERTY  id   ExecIsDeleteTrigger   AS isdelete       OBJECTPROPERTY  id   ExecIsInsertTrigger   AS isinsert       OBJECTPROPERTY  id   ExecIsAfterTrigger   AS isafter       OBJECTPROPERTY  id   ExecIsInsteadOfTrigger   AS isinsteadof       OBJECTPROPERTY id   ExecIsTriggerDisabled   AS  disabled   FROM sysobjects     INNER JOIN sysusers      ON sysobjects uid   sysusers uid       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     EDIT 2  For SQL 2000  SELECT       o name AS trigger name        x  AS trigger owner        USER NAME o uid          s name AS table schema       OBJECT NAME o parent obj  AS table name       OBJECTPROPERTY o id   ExecIsUpdateTrigger   AS isupdate       OBJECTPROPERTY o id   ExecIsDeleteTrigger   AS isdelete       OBJECTPROPERTY o id   ExecIsInsertTrigger   AS isinsert       OBJECTPROPERTY o id   ExecIsAfterTrigger   AS isafter       OBJECTPROPERTY o id   ExecIsInsteadOfTrigger   AS isinsteadof       OBJECTPROPERTY o id   ExecIsTriggerDisabled   AS  disabled   FROM sysobjects AS o     INNER JOIN sysusers      ON sysobjects uid   sysusers uid       INNER JOIN sysobjects AS o2      ON o parent obj   o2 id   INNER JOIN sysusers AS s      ON o2 uid   s uid   WHERE o type    TR

User · Answer

Use this query     SELECT OBJECT NAME parent id  as Table Name    FROM  Database Name  sys triggers   It s simple and useful

User · Answer

this may help   SELECT DISTINCT o  name  AS  Table  FROM     sysobjects  o JOIN     sysobjects  tr     ON  o  id    tr  parent obj  WHERE   tr  type     tr  ORDER BY  Table   Get a list of tables and all their triggers   SELECT DISTINCT o  name  AS  Table   tr  name  AS  Trigger  FROM     sysobjects  o JOIN     sysobjects  tr     ON  o  id    tr  parent obj  WHERE   tr  type     tr  ORDER BY  Table    Trigger

User · Answer

SELECT       sysobjects name AS trigger name    OBJECT NAME parent obj  AS table name  s name AS table schema       USER NAME sysobjects uid  AS trigger owner         OBJECTPROPERTY  id   ExecIsUpdateTrigger   AS isupdate       OBJECTPROPERTY  id   ExecIsDeleteTrigger   AS isdelete       OBJECTPROPERTY  id   ExecIsInsertTrigger   AS isinsert       OBJECTPROPERTY  id   ExecIsAfterTrigger   AS isafter       OBJECTPROPERTY  id   ExecIsInsteadOfTrigger   AS isinsteadof       OBJECTPROPERTY id   ExecIsTriggerDisabled   AS  disabled   FROM sysobjects   INNER JOIN sysusers      ON sysobjects uid   sysusers uid   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     this working for me

User · Answer

SELECT    ServerName       servername     DatabaseName   db name       SchemaName     isnull  s name           TableName      isnull  o name   DDL Trigger        TriggerName    t name      Defininion     object definition  t object id    FROM sys triggers t    LEFT JOIN sys all objects o       ON t parent id   o object id    LEFT JOIN sys schemas s       ON s schema id   o schema id ORDER BY     SchemaName     TableName     TriggerName

User · Answer

C  Cribs  I ended up with this super generic one liner   Hope this is useful to both the original poster and or people who just typed the same question I did into Google   SELECT TriggerRecord name as TriggerName ParentRecord name as ForTableName  FROM sysobjects TriggerRecord  INNER JOIN sysobjects ParentRecord ON TriggerRecord parent obj ParentRecord id  WHERE TriggerRecord xtype  TR    Query Characteristics    Usable with any SQL database  i e  Initial Catalog  Self explanatory One single statement Pasteable directly into most IDE s for most languages

User · Answer

And what do you think about this  Very short and neat        SELECT OBJECT NAME parent id  Table or ViewNM        name TriggerNM        is instead of trigger        is disabled FROM sys triggers WHERE parent class desc    OBJECT OR COLUMN  ORDER BY OBJECT NAME parent id   Name

User · Answer

Necromancing   Just posting because all solutions so far fall a bit short of completeness    SELECT       sch name AS trigger table schema       systbl name AS trigger table name       systrg name AS trigger name       sysm definition AS trigger definition       systrg is instead of trigger        -- https   stackoverflow com questions 5340638 difference-between-a-for-and-after-triggers     -- Difference between a FOR and AFTER triggers      -- CREATE TRIGGER trgTable on dbo Table FOR INSERT UPDATE DELETE     -- Is the same as     -- CREATE TRIGGER trgTable on dbo Table AFTER INSERT UPDATE DELETE     -- An INSTEAD OF trigger is different  and fires before and instead of the insert      -- and can be used on views  in order to insert the appropriate values into the underlying tables      -- AFTER specifies that the DML trigger is fired only when all operations      -- specified in the triggering SQL statement have executed successfully       -- All referential cascade actions and constraint checks also must succeed before this trigger fires       -- AFTER is the default when FOR is the only keyword specified       CASE WHEN systrg is instead of trigger   1 THEN 0 ELSE 1 END AS is after trigger        systrg is not for replication       systrg is disabled      systrg create date       systrg modify date       CASE WHEN systrg parent class   1 THEN  TABLE  WHEN systrg parent class   0 THEN  DATABASE  END trigger class         CASE          WHEN systrg  type     TA  then  Assembly  CLR  trigger          WHEN systrg  type     TR  then  SQL trigger           ELSE         END AS trigger type       -- https   dataedo com kb query sql-server list-triggers      --   CASE WHEN objectproperty systrg object id   ExecIsUpdateTrigger     1     --      THEN  UPDATE   ELSE    END      --   CASE WHEN objectproperty systrg object id   ExecIsDeleteTrigger     1     --      THEN  DELETE   ELSE    END     --   CASE WHEN objectproperty systrg object id   ExecIsInsertTrigger     1     --      THEN  INSERT  ELSE    END     --   AS trigger event                        STUFF                                            SELECT                             type desc AS  text                        -- STRING AGG type desc        AS foo                  FROM sys events AS syse                  WHERE syse object id   systrg object id                 FOR XML PATH      TYPE                  -- GROUP BY syse object id                value    1     nvarchar MAX                   1  2                       AS trigger event groups       --  CASE WHEN systrg parent class   1 THEN  TABLE  WHEN systrg parent class   0 THEN  DATABASE  END trigger class          DROP TRIGGER      sch name           systrg name         AS sql      --  systrg   FROM sys triggers AS systrg   LEFT JOIN sys sql modules AS sysm      ON sysm object id   systrg object id   -- sys objects for view triggers  -- LEFT JOIN sys objects AS systbl ON systbl object id   systrg object id   -- inner join if you only want table-triggers  LEFT JOIN sys tables AS systbl ON systbl object id   systrg parent id   LEFT JOIN sys schemas AS sch      ON sch schema id   systbl schema id   WHERE  1 1    -- AND sch name IS NOT NULL  -- AND sch name IS NULL  -- AND sch name    dbo   -- And here  exclude some triggers with a certain naming schema       AND        -- systbl name IS NULL      -- OR      NOT                systrg name    TRG     systbl name      INSERT History          OR          systrg name    TRG     systbl name      UPDATE History          OR          systrg name    TRG     systbl name      DELETE History              ORDER BY       sch name       systbl name       systrg name

User · Answer

SELECT     OBJECT NAME PARENT OBJECT ID  AS PARENT TABLE      OBJECT NAME OBJECT ID  TRIGGER TABLE        FROM SYS OBJECTS WHERE TYPE    TR

User · Answer

You can also get the body of triggers as following   SELECT      o  name               c  text  FROM        sys objects AS o INNER JOIN  sys syscomments AS c ON      o object id   c id WHERE   o  type     TR

User · Answer

The just above code is incorrect as shown   SELECT      sysobjects name AS trigger name      -- USER NAME sysobjects uid  AS trigger owner      -- s name AS table schema      -- OBJECT NAME parent obj  AS table name      -- OBJECTPROPERTY  id   ExecIsUpdateTrigger   AS isupdate      -- OBJECTPROPERTY  id   ExecIsDeleteTrigger   AS isdelete      -- OBJECTPROPERTY  id   ExecIsInsertTrigger   AS isinsert      -- OBJECTPROPERTY  id   ExecIsAfterTrigger   AS isafter      -- OBJECTPROPERTY  id   ExecIsInsteadOfTrigger   AS isinsteadof      -- OBJECTPROPERTY id   ExecIsTriggerDisabled   AS  disabled   FROM sysobjects     INNER JOIN sysusers      ON sysobjects uid   sysusers uid       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   EXCEPT SELECT OBJECT NAME parent id  as Table Name FROM sys triggers

User · Answer

I had the same task recently and I used the following for sql server 2012 db   Use management studio and connect to the database you want to search  Then execute the following script   Select   tgr   name  as  trigger name     tbl   name  as  table name   from sysobjects tgr   join sysobjects tbl on tgr parent obj   tbl id  WHERE tgr xtype    TR

User · Answer

Here you go       SELECT      so   name  AS  trigger name       USER NAME  so   uid   AS  trigger owner       USER NAME  so2   uid   AS  table schema       OBJECT NAME  so   parent obj   AS  table name       OBJECTPROPERTY   so   id    ExecIsUpdateTrigger   AS  isupdate       OBJECTPROPERTY   so   id    ExecIsDeleteTrigger   AS  isdelete       OBJECTPROPERTY   so   id    ExecIsInsertTrigger   AS  isinsert       OBJECTPROPERTY   so   id    ExecIsAfterTrigger   AS  isafter       OBJECTPROPERTY   so   id    ExecIsInsteadOfTrigger   AS  isinsteadof       OBJECTPROPERTY  so   id    ExecIsTriggerDisabled   AS  disabled   FROM sysobjects AS  so  INNER JOIN sysobjects AS so2 ON so parent obj   so2 Id WHERE  so   type     TR    A couple of things here     Also I see that you were attempting to pull the parent tables schema information  I believe in order to do so you would also need to join the sysobjects table on itself so that you can correctly get the schema information for the parent table  the query above does this  Also the sysusers table wasn t needed in the results so that Join has been removed   tested with SQL 2000  SQL 2005  and SQL 2008 R2

User · Answer

Use This Query        SELECT              DB NAME   AS DataBaseName            S Name AS SchemaName                         T name AS TableName          dbo SysObjects Name AS TriggerName          dbo sysComments Text AS SqlContent      FROM dbo SysObjects      INNER JOIN dbo sysComments ON dbo SysObjects ID   dbo sysComments ID     INNER JOIN sys tables AS T ON sysobjects parent obj   t object id      INNER JOIN sys schemas AS S ON t schema id   s schema id      WHERE dbo SysObjects xType    TR           AND dbo SysObjects Name LIKE  Permit AfterInsert  ----  lt ----- HERE

User · Answer

SELECT tbl name as Table Name trig name as Trigger Name trig is disabled   FROM  sys   triggers  as trig inner join sys tables as tbl on  trig parent id   tbl object id

[sql] Need to list all triggers in SQL Server database with table name and table's schema

Examples related to sql

Examples related to sql-server

Examples related to database

Examples related to tsql

Examples related to triggers