The Missing System Procedure..

Fri Feb 27, 2004 by byrmol

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'

Legacy Comments


Enigma 2004-02-27	re: The Missing System Procedure.. Nice one !!! Thats another for my admin procs collection :)

pifou 2008-03-27	re: The Missing System Procedure.. nice :) I just tested it good with sql2005, thanks!