The Missing System Procedure..

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
CREATE PROC dbo.sp_triggers
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'

Print | posted on Friday, February 27, 2004 10:22 AM


# re: The Missing System Procedure..

left by Enigma at 2/27/2004 11:21 AM Gravatar
Nice one !!! Thats another for my admin procs collection :)

# re: The Missing System Procedure..

left by pifou at 3/27/2008 12:27 AM Gravatar
nice :) I just tested it good with sql2005, thanks!
Comments have been closed on this topic.