Get name of procedure that triggers update trigger

• UncleBoris

  SSCrazy

  Points: 2018

  More actions

  September 21, 2008 at 7:59 pm

  #382740

  Hi, I am trying to find out what stored procedure is causing a certain field to update.

  Currently an update trigger on the table puts the values updated into an audit table but I am not sure how to get the name of the procedure that has the update.

  I have tried object_name(@@procid) but that returns the name of the trigger.

  Thanks

• Bob Pinella

  SSCommitted

  Points: 1737

  More actions

  December 31, 2009 at 11:05 am

  #1097966

  Hello,

  Well one way to do this is to find out which stored procedures are referring to the table that contains the trigger. Once you narrow down the stored procs, you should be able to figure out which one contains the update statement. You can right click on the table and select "View Dependencies" or run the stored proc sp_depends, however in my experience these methods have not always been accurate. As a result, I created my own query:

  Declare @ParamSearchText varchar(100)

  Set @ParamSearchText = 'YourTableName'

  Select

  Case

  When b.type = 'P' then 'Stored Procedure'

  When b.type = 'V' then 'View'

  When b.type = 'FN' then 'Scalar Function'

  When b.type = 'IF' then 'Inline Table-Valued Function'

  When b.type = 'TF' then 'Table-Valued Function'

  When b.type = 'TR' then 'Trigger'

  Else b.type_desc

  End as ObjectType,

  c.name + '.' + b.name as ObjectName

  From

  sys.sql_modules a,

  sys.objects b,

  sys.schemas c

  Where

  a.[object_id] = b.[object_id]

  and b.[schema_id] = c.[schema_id]

  and a.definition like '%' + @ParamSearchText + '%'

  Order by

  ObjectType,

  ObjectName

  Just enter your table name in the @ParamSearchText parameter and you will get a list of objects that reference the table in question.

  Bob Pinella