Get Trigger Metadata

Applicability:

SQL Server 2000: N/A

SQL Server 2005: Tested

SQL Server 2008: Tested

SQL Server 2008R2: Tested

SQL Server 2012: Tested

SQL Server 2014: Tested

SQL Server 2016: Tested

SQL Server 2017: Not Tested

Credits:

Author:Phil Factor/ChillyDBA

Date: 5 Jun 2020

Description

A small collection of queries to produce database-level reports on triggers. These should mostly run as-is but can be easily customized.

There are 2 main types of report queries:

• 1. Summary List - containing schema/object names and any stored extended properties. Ideal for documentation

  2. Detail List - containing schema/object names plus object type, component columns and code. Can be used for documentation, but also valuable for use when constructing Dynamic SQL queries

Where there are multiple variants on the same query, one will utilize the INFORMATION_SCHEMA views and the other the Dynamic Management Views. This is generally for Detail type queries as some detail levels are only available in one source

Code

Get a summary of all Triggers:

--all triggers in the database and their parent table

SELECT

ob.name AS Trigger_Name

,COALESCE(EP.value, '') AS Documentation

,OBJECT_SCHEMA_NAME(ob.parent_object_id) + '.' + OBJECT_NAME(ob.parent_object_id) AS Parent_Object_Name

,COALESCE(EPParent.value, '') AS Documentation

FROM sys.objects AS ob

LEFT OUTER JOIN sys.extended_properties AS EP

ON EP.major_id = ob.object_id

AND EP.name = 'MS_Description' --the microsoft convention

LEFT OUTER JOIN sys.extended_properties AS EPParent

ON EPParent.major_id = ob.parent_object_id

AND EPParent.minor_id = 0

AND EPParent.name = 'MS_Description' --the microsoft convention

WHERE OBJECTPROPERTY(ob.object_id, 'IsTrigger') = 1;

Get a summary of all Triggers including a comma separated list of triggering events:

SELECT

@@SERVERNAME AS Server_Name

,DB_NAME() AS Database_Name

,CONVERT(CHAR(25),name) AS Trigger_Name

,CONVERT(CHAR(32),COALESCE(OBJECT_SCHEMA_NAME(parent_ID)+'.'+ OBJECT_NAME(parent_ID),'Database ('+DB_NAME()+')')) AS Parent_Object_Name

,is_disabled As Is_Trigger_Disabled

,CASE

WHEN is_instead_of_trigger = 1 THEN 'INSTEAD OF '

ELSE 'AFTER '

END

+ STUFF (--get a list of events for each trigger

(

SELECT ', ' + type_desc

FROM sys.trigger_events te

WHERE te.object_ID = sys.triggers.object_ID

FOR XML PATH(''), TYPE

).value ('.', 'varchar(max)')

,1,2,''

) AS events

FROM sys.triggers

Get a summary of all Triggers with a count of the number of dependencies:

SELECT

@@SERVERNAME AS Server_Name

,DB_NAME() AS Database_Name

,COALESCE(OBJECT_SCHEMA_NAME(parent_id) + '.','') + CONVERT(CHAR(32),name) AS Trigger_name

,COUNT(*) AS #Trigger_Dependancies

FROM sys.triggers

INNER JOIN sys.SQL_Expression_dependencies

ON [referencing_id] = object_ID

GROUP BY name, parent_id

ORDER BY count(*) DESC;

Get details of all Triggers with a list of their dependencies:

SELECT

@@SERVERNAME AS Server_Name

,DB_NAME() AS Database_Name

,CONVERT(CHAR(32),name) AS Trigger_Name

,CONVERT(CHAR(32),COALESCE([referenced_server_name]+'.','')

+ COALESCE([referenced_database_name]+'.','')

+ COALESCE([referenced_schema_name]+'.','')+[referenced_entity_name])

AS Referenced_Object_Name

FROM sys.triggers

INNER JOIN sys.SQL_Expression_dependencies

ON [referencing_id]=object_ID

Get a summary of all Triggers with the length of the trigger code:

SELECT

@@SERVERNAME AS Server_Name

,DB_NAME() AS Database_Name

,CONVERT(CHAR(32),COALESCE(OBJECT_SCHEMA_NAME(t.object_ID)+'.','') + name) AS Trigger_Name

,CONVERT(CHAR(32),COALESCE(OBJECT_SCHEMA_NAME(parent_ID)+'.' + OBJECT_NAME(parent_ID),'Database ('+DB_NAME()+')')) AS Parent_Object_Name

,LEN(definition) AS Trigger_Code_Length --the length of the definition

FROM sys.SQL_modules m

INNER JOIN sys.triggers t

ON t.object_ID=m.object_ID

ORDER BY Trigger_Code_Length DESC;

Get a summary of all Tables with counts of each type of Trigger:

SELECT

@@SERVERNAME AS Server_Name

,DB_NAME() AS Database_Name

,CONVERT(CHAR(32),COALESCE(OBJECT_SCHEMA_NAME(parent_ID)+'.' + OBJECT_NAME(parent_ID),'Database ('+DB_NAME()+')')) AS Table_Name

,#Triggers AS Total_Triggers

,CONVERT(SMALLINT,OBJECTPROPERTYEX(Parent_ID, N'TABLEDeleteTriggerCount')) AS Delete_Triggers

,CONVERT(SMALLINT,OBJECTPROPERTYEX(Parent_ID, N'TABLEInsertTriggerCount')) AS Insert_Triggers

,CONVERT(SMALLINT,OBJECTPROPERTYEX(Parent_ID, N'TABLEUpdateTriggerCount')) AS Update_Triggers

FROM

(

SELECT

COUNT(*) AS #Triggers

,parent_ID AS Parent_ID

FROM sys.triggers

WHERE OBJECTPROPERTYEX(parent_ID, N'IsTable') =1

GROUP BY parent_ID

) TablesOnly

Get details of all SERVER level triggers:

SELECT

@@SERVERNAME AS Server_Name

,DB_NAME() AS Database_Name

,name AS Server_Trigger_Name

,definition AS Server_Trigger_Text

FROM sys.server_SQL_modules m

INNER JOIN sys.server_triggers t

ON t.object_ID=m.object_ID

Search all Trigger code for a (parameterized) search string:

DECLARE @SearchString NVARCHAR(MAX)

SELECT @SearchString = 'SELECT'

SELECT

COALESCE(OBJECT_SCHEMA_NAME(object_id) + '.','') + CONVERT(CHAR(32),name) AS Trigger_Name

,'...'+substring(definition, SearchHit - 20,120) +'...' AS Trigger_Code_Extract

FROM

(

SELECT

name

,definition

,t.object_ID

,charindex(@SearchString ,definition) AS SearchHit

FROM sys.SQL_modules m

INNER JOIN sys.triggers t

ON t.object_ID=m.object_ID

) AS f

WHERE SearchHit > 0;