Search

Friday, March 23, 2012

Show all triggers in a database with source code

Run below query to list all triggers in a database with source code:


SELECT 
Object_name(T.parent_id) AS [Table Name],
T.[Name] AS [Trigger Name],
T.is_disabled AS [isDisabled],
T.is_Instead_of_trigger AS [isInsteadOfTrigger],
M.definition AS [Source Code], 
T.create_date AS [Created on],
T.modify_date AS [Modified on]
FROM 
SYS.TRIGGERS T INNER JOIN SYS.SQL_MODULES M 
ON T.Object_id = M.Object_ID
WHERE 
T.[Type] = 'TR' AND
T.is_ms_shipped = 0 AND
T.[parent_id] > 0
ORDER BY 
Object_name(T.parent_id), T.[Name]
GO

No comments:

Post a Comment