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 SELECTob.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 DocumentationFROM sys.objects AS obLEFT OUTER JOIN sys.extended_properties AS EPON EP.major_id = ob.object_idAND EP.name = 'MS_Description' --the microsoft convention LEFT OUTER JOIN sys.extended_properties AS EPParentON EPParent.major_id = ob.parent_object_idAND EPParent.minor_id = 0AND 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,CASEWHEN is_instead_of_trigger = 1 THEN 'INSTEAD OF 'ELSE 'AFTER 'END+ STUFF (--get a list of events for each trigger (SELECT ', ' + type_descFROM sys.trigger_events teWHERE te.object_ID = sys.triggers.object_IDFOR XML PATH(''), TYPE).value ('.', 'varchar(max)'),1,2,'') AS eventsFROM 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_DependanciesFROM sys.triggersINNER JOIN sys.SQL_Expression_dependenciesON [referencing_id] = object_IDGROUP BY name, parent_idORDER 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_NameFROM sys.triggersINNER JOIN sys.SQL_Expression_dependenciesON [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 mINNER JOIN sys.triggers tON t.object_ID=m.object_IDORDER 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_TriggersFROM(SELECTCOUNT(*) AS #Triggers,parent_ID AS Parent_IDFROM sys.triggersWHERE OBJECTPROPERTYEX(parent_ID, N'IsTable') =1GROUP 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_TextFROM sys.server_SQL_modules mINNER JOIN sys.server_triggers tON 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'SELECTCOALESCE(OBJECT_SCHEMA_NAME(object_id) + '.','') + CONVERT(CHAR(32),name) AS Trigger_Name,'...'+substring(definition, SearchHit - 20,120) +'...' AS Trigger_Code_ExtractFROM(SELECTname,definition,t.object_ID,charindex(@SearchString ,definition) AS SearchHitFROM sys.SQL_modules mINNER JOIN sys.triggers tON t.object_ID=m.object_ID) AS fWHERE SearchHit > 0;