SQL Server – get all triggers

Here is an often used SQL statement to get a list of Triggers, in SQL Server. I’m posting this here for my own convenience and perhaps someone else will benefit.

SELECT
  [so].[name] AS sys_obj,
  so.type,
  USER_NAME([so].[uid]) AS obj_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';

 

 


				
							
			

Leave a Reply

Your email address will not be published. Required fields are marked *