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..
CREATE PROC dbo.sp_triggers
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
WHERE type = 'TR'
| posted on Friday, February 27, 2004 10:22 AM