[Transact-SQL] Finding Triggers in SQL

to get an overview of of the triggers:

sql Code:

Original - sql Code
SELECT OBJECT_NAME(parent_obj) AS TableName,<br>OBJECT_NAME(id) AS TriggerName<br>FROM sysobjects<br>WHERE OBJECTPROPERTY(id, 'IsTrigger') =1

SELECT OBJECT_NAME(parent_obj) AS TableName,
OBJECT_NAME(id) AS TriggerName
FROM sysobjects
WHERE OBJECTPROPERTY(id, 'IsTrigger') =1

to get a list of all triggers

sql Code:

Original
- sql Code

SELECT 'CREATE TRIGGER ' + user_name(trig.uid) + '.' + trig.name AS [CREATE
TRIGGER]
, 'ON ' + user_name(tab.uid) + '.' + tab.name AS [ON]
, CASE WHEN ObjectProperty( trig.id, 'ExecIsInsteadOfTrigger') = 1 THEN
'INSTEAD OF ' ELSE 'FOR ' END
+ SUBSTRING(CASE WHEN ObjectProperty( trig.id, 'ExecIsInsertTrigger') = 1
THEN ', INSERT' ELSE '' END
+ CASE WHEN ObjectProperty( trig.id, 'ExecIsUpdateTrigger') = 1 THEN ',
UPDATE' ELSE '' END
+ CASE WHEN ObjectProperty( trig.id, 'ExecIsDeleteTrigger') = 1 THEN ',
DELETE' ELSE '' END, 3, 100)  AS [FOR]
FROM sysobjects AS trig
INNER JOIN sysobjects AS tab ON tab.id = trig.parent_obj
WHERE trig.type = 'TR'
ORDER BY tab.name

found solution at sqlmonster.com

[Transact-SQL] Finding Triggers in SQL

Programming

Related Pages