CSS - DOT NET - SQL SERVER - SEO - UTILITIES - DOWNLOADS

Your Source for Software / Utilities / Tips and Tricks

I wrote this little gem when I was trying to find an easy way to list all the triggers in the active database..
The idea of running sp_helptrigger on all the tables seemed too painfull. To see the creation text execute the proc with a 1 after it...
Enjoy!

Script

Use Master

GO

Create   PROCEDURE sp_HelpAllTriggers
(  
@ShowText BIT = 0
)
AS
    IF @ShowText = 0
        BEGIN
            SELECT o2.name AS TableName, o1.name AS TriggerName,
                CASE (OBJECTPROPERTY(o1.id,'ExecIsTriggerDisabled'))
                    WHEN 0
                    THEN 'True' 
                    WHEN '1'
                        THEN 'False'
                END AS Enabled,           
                UpdateTrigger       = OBJECTPROPERTY(o1.id,'ExecIsUpdateTrigger'),
                InsertTrigger       = OBJECTPROPERTY(o1.id,'ExecIsInsertTrigger'),
                DeleteTrigger       = OBJECTPROPERTY(o1.id,'ExecIsDeleteTrigger'),
                AfterTrigger        = OBJECTPROPERTY(o1.id,'ExecIsAfterTrigger'),
                InsteadOfTrigger    = OBJECTPROPERTY(o1.id,'ExecIsInsteadOfTrigger')
	          FROM SysObjects o1
                JOIN  SysObjects o2
                    ON  o1.parent_obj = o2.id                    
            WHERE o1.type = 'tr'
            ORDER BY 1
        END
    ELSE
        BEGIN
            SELECT o2.name AS TableName ,o1.name AS TriggerName, text,

                CASE (OBJECTPROPERTY(o1.id,'ExecIsTriggerDisabled'))
                    WHEN 0
                        THEN 'True'
                    WHEN '1'
                        THEN 'False'
                END AS Enabled,
                
                UpdateTrigger       = OBJECTPROPERTY(o1.id,'ExecIsUpdateTrigger'),
                InsertTrigger       = OBJECTPROPERTY(o1.id,'ExecIsInsertTrigger'),
                DeleteTrigger       = OBJECTPROPERTY(o1.id,'ExecIsDeleteTrigger'),
                AfterTrigger        = OBJECTPROPERTY(o1.id,'ExecIsAfterTrigger'),
                InsteadOfTrigger    = OBJECTPROPERTY(o1.id,'ExecIsInsteadOfTrigger')
                
            FROM SysObjects o1
                JOIN SysObjects o2
                    ON o1.parent_obj = o2.id
                JOIN SysComments sc
                    ON o1.id = sc.id
            WHERE o1.type = 'tr' 
            ORDER BY sc.id, sc.colid, 1
        END