Tuesday, April 27, 2010

Get all triggers in a SQL Database

Ever need to get all the triggers in a SQL database.  This script will return them;


SELECT S2.[name] TableName, S1.[name] TriggerName,
WHEN S2.deltrig =  THEN 'Delete'
WHEN S2.instrig = THEN 'Insert'
WHEN S2.updtrig = THEN 'Update'
END 'TriggerType' , 'S1',s1.*,'S2',s2.*
FROM sysobjects S1 JOIN sysobjects S2 ON S1.parent_obj = S2.[id] WHERE S1.xtype='TR' order by TableName

No comments: