Get Trigger Metadata

Cool Tips‎ > ‎Tables and Triggers‎ > ‎

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:

--all triggers in the database and their triggering event(s)

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:

--all triggers in the database and the number of objects that they operate on (ie 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:

--all triggers in the database and the list of objects that they operate on (ie 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:

--all triggers in the database and the length of the trigger code

--good for quickly identifying lengthy triggers that might cause locking/blocking in a busy system

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:

--all tables in the database and the count of each type of trigger defined on them

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:

--all SERVER level triggers with code/definition

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:

--search all triggers in the database for a specified 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;