Monday, August 12, 2013

T-SQL script to get list of all the tables having triggers in a database with trigger name.



Approach:
  • Sys.objects table contains information about all the objects of a database
  • We are filtering the trigger objects with TYPE = ‘TR’
  • Triggers create on tables so parent_object_id column contains the object id of the table on which trigger created.
  • By using the OBJECT_NAME, getting the name of parent table
SELECT
     OBJECT_NAME(parent_object_id) As Table_Name,
     name AS Trigger_Name
FROM sys.objects
WHERE [TYPE] = 'TR'
 
 

1 comment: