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.

Solution 1

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'

Solution 2

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

Solution 3

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'

Solution 4

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'

Solution 5

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

View more solutions

Comments

[Joe Stefanelli]

Odd. Works for me on 2005 and 2008 instances.

[JNK]

@Ronnie - What version of SQL Server are you using? This may not work on older (2000 and older) builds...

[Joe Stefanelli]

Your original query returns no results in Adventureworks either. Looks like it's the join to sysusers that throws it off.

[MsBao]

Just noticed that myself. My original query was a working version.

[Joe Stefanelli]

Edited my answer and provided a query with sysusers commented out.

[MsBao]

Thanks, Joe. Is there a version of the query that would be compatible with both SQL Server 2000?

[JNK]

@Ronnie - My comment was because they added some new system views in the newer versions of SQL Server. Off the top of my head I don't know which they were. Joe may know and answer, but it certainly won't hurt to TRY your query as written in an older version.

[Joe Stefanelli]

@Ronnie: SQL Server 2000 does not have the concept of schemas. Owner would be equivalent for that version. See my edited answer for a query for 2000.

[MsBao]

@Joe - I see the edit # 2 version doesn't return results against 2008r2. Is that as it should be? I'm writing a tool for sql server and would like to make it backwards compatible.

[Joe Stefanelli]

@Ronnie: Edit #2 is intended for SQL 2000 only. The concept/use of schemas is completely different between 2000 and 2005/8.

[MsBao]

Just making sure. I don't have an instance of 2000 to test with.

[Joe Stefanelli]

No. This works where owner/schema were synonymous in SQL Server 2000. This does not return the correct schema when run on a 2005/8 database.

[Chris Smith]

nice, thanks for the sql 2000 version as well - some of us still are stuck in that world.

[ErikE]

It's perfectly fine to do SELECT Alias = Expression. It hasn't been deprecated. What has been deprecated is SELECT 'Alias' = Expression. I got this information from your own link! Please correct the misinformation.

[Yu Shen]

The table [Database_Name].sys.triggers for my case is empty. Mine is with Microsoft SQL Server 2017

[Yu Shen]

Resulted in empty result. It should not be. Mine is Microsoft SQL server 2017.

[Eric K]

@YuShen, you need to change [Database_Name] to the name of your actual database

[Acapulco]

If possible, try to add some explanation on what does your code do to make the answer more useful.

[Christoph]

The Edit #2 is still the best on SQLServer2016 - the two other versions do not show View-Triggers but only Table-Triggers.

[Marco Siffert]

This should be higher up. Using sys.triggers seems like the cleanest solution.