Which stored proc(s) updating certain specific columns in a table

Question

I am trying to research on why certain columns in a table were not updated. To get to root cause, I am trying to identify which stored proc(s) could be updating those specific columns in the table.

So..

How do I identify stored proc(s) that would update columns aa,bb,cc in the table123 in a given database?

There could be many stored procs updating this table. This table could have many other columns. Is it possible to identify?

Answer 1

Run this query

SELECT Name
FROM sys.procedures
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%put text here%'

Answer 2

I would strongly recommend getting a tool like SQL Digger - we use this regularly at work to hunt down data in SPs, etc. for precisely the kind of problem (among many others) that you are describing.

Also Redgate has a similar product (SQL Search)

Answer 3

If the search through the source code comes up inconclusive you can set up a trigger on the table and then use extended events to get the full TSQL trace of the triggering statement.

Answer 4

Red Gate Software's SQL Prompt 5 has a Column Dependencies feature that might be useful in this situation. You hover over a column name in a script, and up pops a window containing a list of objects that use that column, including stored procedures.

You can download a 14-day trial for free, to see if this feature works for you.

Paul Stephenson
SQL Prompt Project Manager
Red Gate Software