List all triggers in SQL Server database with table name and schema

Sample query:


SELECT
@@servername as ServerName,
db_name() as DatabaseName,
[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'

Here is and another version where you can the code of the trigger:


SELECT
ServerName = @@servername,
DatabaseName = db_name(),
SchemaName = isnull( s.name, '' ),
TableName = isnull( o.name, 'DDL Trigger' ),
TriggerName = t.name,
Defininion = object_definition( t.object_id )
FROM sys.triggers t
LEFT JOIN sys.all_objects o
ON t.parent_id = o.object_id
LEFT JOIN sys.schemas s
ON s.schema_id = o.schema_id
ORDER BY
SchemaName,
TableName,
TriggerName

, ,

No comments yet.

Leave a Reply

*