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:
Summary List - containing schema/object names and any stored extended properties. Ideal for documentation
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;