Listing All Triggers in the SQL Server Database

 /*********************************************************************************/

/* Listing All Triggers in the SQL Server - SQL Server Tutorial */

/* Need to list all triggers in SQL Server database with table */

/* List triggers in SQL Server database - SQL Server Data */

/*********************************************************************************/


/*********************************************************************************/

   /*Example 1*/

/*********************************************************************************/


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



/*********************************************************************************/

   /*Example 2*/

/*********************************************************************************/


 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'


/*********************************************************************************/

   /*Example 3*/

/*********************************************************************************/


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' 



/*********************************************************************************/

   /*Example 4*/

/*********************************************************************************/


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'




/*********************************************************************************/

/*********************************************************************************/

ANIL SINGH

Anil Singh is an author, tech blogger, and software programmer. Book writing, tech blogging is something do extra and Anil love doing it. For more detail, kindly refer to this link..

My Tech Blog - https://www.code-sample.com/
My Books - Book 1 and Book 2

www.code-sample.com/. Powered by Blogger.
^