In the following lesson, you can learn how to list all triggers in SQL Server Database.
I had the same task recently and I used the following for SQL Server 2012 DB. Use the management studio and connect to the database you want to search. Then execute the following script for a list of all triggers in SQL Server database with table name and table’s schema.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | SELECT
[so].[name] AS [trigger_name],
USER_NAME([so].[uid]) AS [trigger_owner],
USER_NAME([so2].[uid]) AS [table_schema],
OBJECT_NAME([so].[parent_obj]) AS [table_name],
OBJECTPROPERTY( [so].[id], 'ExecIsUpdateTrigger') AS [isupdate],
OBJECTPROPERTY( [so].[id], 'ExecIsDeleteTrigger') AS [isdelete],
OBJECTPROPERTY( [so].[id], 'ExecIsInsertTrigger') AS [isinsert],
OBJECTPROPERTY( [so].[id], 'ExecIsAfterTrigger') AS [isafter],
OBJECTPROPERTY( [so].[id], 'ExecIsInsteadOfTrigger') AS [isinsteadof],
OBJECTPROPERTY([so].[id], 'ExecIsTriggerDisabled') AS [disabled]
FROM sysobjects AS [so]
INNER JOIN sysobjects AS so2 ON so.parent_obj = so2.Id
WHERE [so].[type] = 'TR' |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
SELECT
o.name AS trigger_name
,'x' AS trigger_owner
/*USER_NAME(o.uid)*/
,s.name AS table_schema
,OBJECT_NAME(o.parent_obj) AS table_name
,OBJECTPROPERTY(o.id, 'ExecIsUpdateTrigger') AS isupdate
,OBJECTPROPERTY(o.id, 'ExecIsDeleteTrigger') AS isdelete
,OBJECTPROPERTY(o.id, 'ExecIsInsertTrigger') AS isinsert
,OBJECTPROPERTY(o.id, 'ExecIsAfterTrigger') AS isafter
,OBJECTPROPERTY(o.id, 'ExecIsInsteadOfTrigger') AS isinsteadof
,OBJECTPROPERTY(o.id, 'ExecIsTriggerDisabled') AS [disabled]
FROM sysobjects AS o /* INNER JOIN sysusers ON sysobjects.uid = sysusers.uid
*/
INNER JOIN sysobjects AS o2
ON o.parent_obj = o2.id
INNER JOIN sysusers AS s
ON o2.uid = s.uid
WHERE o.type = 'TR' |
Leave a Comment