Translate

Tuesday, April 9, 2013

SQL - List All Triggers

I'm working on a large database with over 2400 tables and I wanted to know what all of the triggers are and where they're located.

This is the query I ran to get that information:

SELECT
   ServerName   = @@servername,
   DatabaseName = db_name(),
   SchemaName   = t.TABLE_SCHEMA,
   TableName    = object_name( o.parent_obj ),
   TriggerName  = o.name,
   Definition   = c.text
FROM sysobjects o
   JOIN syscomments c ON o.id = c.id
   JOIN INFORMATION_SCHEMA.TABLES t ON object_name( o.parent_obj ) = t.TABLE_NAME
WHERE o.type = 'TR'
ORDER BY SchemaName, TableName, TriggerName

No comments:

Post a Comment

Thank you for commenting!