In one of the my earlier articles related to triggers, I had written about how to get the list of triggers along with its different properties using sys.triggers & OBJECTPROPERTY. It was quite a big script to get these details. Recently, when I was working on some system level stored procedures, I got a shortcut to achieve list of triggers along with its schema using sp_MSforeachtable & sp_helptrigger
Given below is the script.
--This script is compatible with SQL Server 2005 and above. USE AdventureWorks2012 GO EXEC sp_MSforeachtable @command1 = 'sp_helptrigger ''?''' ,@whereand = 'And Object_id In (Select parent_id From sys.triggers)'; --OUTPUT