Sunday, 15 January 2017

SQL Server - List all Triggers in a Database

You can use the below query to list ass Triggers in your database.
It lists the Trigger name, table name and the type of trigger (update / delete / insert etc).

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' 

2 comments:

  1. wow very well information you had shared here. This is important thing because when we are running a business first we have to make our focusing area and what we are ready for serve among the people. And really i am much inspired with this 10 steps. Surely it will be useful for craeting the better mindset among our business. Thank you and i am looking forward your more information here

    Digital Marketing Company in Chennai

    ReplyDelete
  2. Very nice post here and thanks for latest smartphone applications it .I always like and such a super colors of phone for these post.Excellent and very cool idea and great models and different kinds of the more information's.

    Seo Company in Chennai

    ReplyDelete