CODEWITHSUNDEEP
sql-server-2008
dotnet-practitioner
dotnet-practitioner

Reputation: 14148

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

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?

Upvotes: 0

Views: 78

Answers (4)

Paul Stephenson
Paul Stephenson

Reputation: 69410

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

Upvotes: 0

Martin Smith
Martin Smith

Reputation: 453328

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.

Upvotes: 0

RC_Cleland
RC_Cleland

Reputation: 2294

Run this query 

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

Upvotes: 1

Bob Palmer
Bob Palmer

Reputation: 4762

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)

Upvotes: 0

Related Questions