Need to List All Triggers in SQL Server Database with Table Name and Table's Schema

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

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'

How to find all trigger associated with a table with SQL Server?

You can do this simply with SSMS. Just go to your table name and expand the Triggers node to view a list of triggers associated with that table. Right click to modify your trigger.

List All Triggers from all databases in one server

This will give what you want to get

DECLARE @QRY VARCHAR(MAX) ='';

SELECT @QRY =@QRY + ' select  s.name AS table_schema 
    ,OBJECT_NAME(TR.parent_id) AS table_name 
    ,OBJECTPROPERTY( SO.object_id, ''ExecIsUpdateTrigger'') AS isupdate 
    ,OBJECTPROPERTY( SO.object_id, ''ExecIsDeleteTrigger'') AS isdelete 
    ,OBJECTPROPERTY( SO.object_id, ''ExecIsInsertTrigger'') AS isinsert 
    ,OBJECTPROPERTY( SO.object_id, ''ExecIsAfterTrigger'') AS isafter 
    ,OBJECTPROPERTY( SO.object_id, ''ExecIsInsteadOfTrigger'') AS isinsteadof 
    ,OBJECTPROPERTY(SO.object_id, ''ExecIsTriggerDisabled'') AS [disabled]
FROM ['+name+'].SYS.TRIGGERS TR
INNER JOIN ['+name+'].sys.tables t     ON TR.parent_id = t.object_id 
INNER JOIN ['+name+'].sys.objects SO ON TR.object_id = SO.object_id
INNER JOIN ['+name+'].sys.schemas s     ON t.schema_id = s.schema_id 
WHERE OBJECT_NAME(parent_id) IN (''accgrp'',''portinfo'')
UNION ALL
' 
FROM SYS.DATABASES
WHERE name not IN ('master', 'model', 'msdb', 'tempdb', 'resource',
       'distribution' , 'reportserver', 'reportservertempdb','jiradb')

SELECT @QRY = SUBSTRING(@QRY,1,LEN(@QRY)-12)

EXEC( @QRY)

What I have done?

1. Queried sys.databases Table to get user created Databases.

2. Appended the query by taking Database name column of sys.databases as prefix of tables. and UNION ALL as suffix of the Query

3. Stored it in a variable @QRY by internal looping with SELECT @QRY =@QRY +

4. Removed last UNION ALL with Substring

5. Executed the dynamic code.

How to get the list of active triggers on a database?

select objectproperty(object_id('TriggerName'), 'ExecIsTriggerDisabled') 

1 means true, 0 means false obviously

Use Jeff O's query and modify it a bit

SELECT  
       TAB.name as Table_Name 
     , TRIG.name as Trigger_Name
     , TRIG.is_disabled  --or objectproperty(object_id('TriggerName'), 'ExecIsTriggerDisabled')
FROM [sys].[triggers] as TRIG 
inner join sys.tables as TAB 
on TRIG.parent_id = TAB.object_id 

or add it as a where clause.

where TRIG.is_disabled = 0 -- or 1 depends on what you want

List of triggers for each table?

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'

Hope this helps

How do I list (or export) the code for all triggers in a database?

Your could try the 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'

how to see list of tables and list of trigger(and sequences)?

In oracle, you cannot find which sequence is used on which table, it is not associated on table level. For that you need to find the code, and search for the sequence where it is been used, may be it is used on before insert trigger or in the PL/SQL code.

For trigger, you can see the data dictionary views

select table_name,
       trigger_name as object_name,
       'TRIGGER' object_type 
from ALL_TRIGGERS

EDIT
My way of finding a sequence is

1. Suppose i want to check where 'Seq_ID' is been used .
2. Select * from dba_source where lower(text) like '%seq_id.nextval%';
3. This will tell me the code where the sequence is been referenced ,probably you can find insert statement in the code ,from that you can find that this sequence is associated with which table
4. Or it will give you the trigger code ,and from the trigger you can find which table is referred.

SQL Server get table name in database trigger

Here's a setup that I use on many of my databases. It demonstrates most of the things that you asked about:

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [Meta].[DdlEvents](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [CreatedOn] [datetime] NULL,
    [CreatedBy] [sysname] NULL,
    [CreateBy2] [sysname] NULL,
    [SchemaName] [sysname] NULL,
    [ObjectName] [sysname] NULL,
    [HostName] [sysname] NULL,
    [ProgramName] [sysname] NULL,
    [SqlCommand] [nvarchar](max) NULL,
    [XmlData] [xml] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO


CREATE TRIGGER [DDLTrigger_LogDDL]
    ON DATABASE
    FOR DDL_DATABASE_LEVEL_EVENTS
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE
        @EventData XML = EVENTDATA();

    INSERT INTO Meta.DdlEvents( 
        SqlCommand, 
        SchemaName,
        ObjectName,
        HostName,
        ProgramName,
        XmlData 
        ) 
    VALUES ( 
        @EventData.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'NVARCHAR(MAX)'),
        @EventData.value('(/EVENT_INSTANCE/SchemaName)[1]',  'NVARCHAR(255)'), 
        @EventData.value('(/EVENT_INSTANCE/ObjectName)[1]',  'NVARCHAR(255)'),
        HOST_NAME(),
        PROGRAM_NAME(),
        @EventData 
        );
END
GO

ENABLE TRIGGER [DDLTrigger_LogDDL] ON DATABASE
GO

Specifically, you use the EVENTDATA() function within the Database DDL trigger to get the Event XML, then you can extract the object(table's) object and schema at the /EVENT_INSTANCE/SchemaName and /EVENT_INSTANCE/ObjectName nodes.

---

Follow-up on Martin's comment below for the OP, a DDL triger only fires on DDL events, that is commands like CREATE,ALTER and DROP. It does not fire for DML events like INSERT, UPDATE and DELETE. So, if that is what you need, then this answer would not work, and yes, you would need a trigger on every table.

SQL query to get all the heading names under a specific schema name

Try using an additional filter

   WHERE TYPE = 'T'

this will limit your result to tables.

More details can be found here

How to list all event triggers(DDL Triggers) present in a database in postgres

These are available in system table pg_event_trigger

---

Related Topics