How to get all triggers text in the DB at once

Aim: How to get the text for all delete triggers in the database at once.

Analysis:

For each table, the ID for each of their triggers (INS, UPD, DEL) are stored in DELTRIG, INSTRIG, AND UPDTRIG columns in the sysobjects table.



The text (t-SQL code) of triggers and all other t-SQL code are store in TEXT column in syscomments. To have the text with proper line breaks, change the Query Editor to "Result to Text", and increase the number for the "maxiumn number of character displayed" in the Query Editor's Option dialogOther trigger information are stored in the SYSOBJECTS table also, including NAME, ID, Parent_ID. Both The Parent_ID and DELTRIG value for trigger in the SYSOBJECTS table are refering to the ID of table that own the trigger



Solution:

1. Get all the delete triggers' text from the database

SELECT SC.TEXT
FROM SYSCOMMENTS SC
INNER JOIN (SELECT DELTRIG FROM SYSOBJECTS WHERE XTYPE='U') DT ON SC.ID = DT.DELTRIG


2. Get list of tables and associated triggers in the database

SELECT SO.NAME, ST.NAME,
CASE WHEN ST.ID = SO.DELTRIG THEN 'DEL'
WHEN ST.ID = SO.UPDTRIG THEN 'UPD'
WHEN ST.ID = SO.INSTRIG THEN 'INS' END TRIGGERTYPE
FROM SYSOBJECTS SO
INNER JOIN SYSOBJECTS ST ON SO.ID = ST.parent_obj
WHERE SO.xtype = 'U'