Credits:
Description
- 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
Code
--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; |
--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 |
--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; |
--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 |
--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; |
--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 |
--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 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; |