Nathan Prather

I’m copying this here from the original post in case I forget it, here’s the link to the original content:

http://weblogs.sqlteam.com/davidm/archive/2004/02/27/999.aspx

And here’s the goods posted way back in 2004 by David M:

Doing some documentation today and it came to triggers.

SQL Server has “sp_helptrigger” but it must be passed in a table name.
I looked around on the web and have found a few bits of code (a couple of UDF’s and some basic ones) so I thought i would create one based on the original and add it to master for reuse in any db.
The code is based on the select statement in “sp_helptrigger” with 2 extra column indicating table name and the triggers status.
I’m hoping the next release of SQL will fix this… Does anyone on the beta know?
It’s only tested in SQL2k..

USE master
GO
CREATE PROC dbo.sp_triggers
AS
SET NOCOUNT ON
SELECT trigger_name = name, trigger_owner = USER_NAME(uid), table_name = OBJECT_NAME(parent_obj),
isupdate = OBJECTPROPERTY( id, ‘ExecIsUpdateTrigger’), isdelete = OBJECTPROPERTY( id, ‘ExecIsDeleteTrigger’),
isinsert = OBJECTPROPERTY( id, ‘ExecIsInsertTrigger’), isafter = OBJECTPROPERTY( id, ‘ExecIsAfterTrigger’),
isinsteadof = OBJECTPROPERTY( id, ‘ExecIsInsteadOfTrigger’),
status = CASE OBJECTPROPERTY(id, ‘ExecIsTriggerDisabled’) WHEN 1 THEN ‘Disabled’ ELSE ‘Enabled’ END
FROM sysobjects
WHERE type = ‘TR’