Is there a way to get all the stored procedures that can update a specific column in SQL?_问答_开发者

I've got a field in a table that changed value unexpectedly. Clearly, a stored procedure caused that (I've already searched the fe开发者_运维知识库w triggers we're using, and no login has update grants on any table in our database), and I'm in the process of finding which one did.

If I was say in Visual Studio, I would just do a "Find all references" on the field, and the task would be easy.

Is there an equivalent tool for SQL ? I know about the "find object dependencies" feature of SSMS, but this would only return the stored procedures (and views) using the table, not the specific field. (and unfortunately my table is joined in literally thousands of SQL queries)

The column name is 'Active', so doing a text search on my database schema is not gonna help a lot either (I've got hundreds of tables with such a field)

So basically the two options I see are :

writing a complicated regex to match the updates. Writing such a regex is probably a huge task (because of all the syntaxix subtleties of SQL).
using a tool that does just this.

Do you know of such a tool (or such a regex, or another way to do this) ?

Why is it clear to you that a stored procedure change the value? Have you denied direct table access to all the database logins? Is all your access to the database confined to stored procedures? If so, it should be a relatively simple matter of searching the stored procedures which depend upon that table and inspecting them.

You could use a trigger to track the change to a column. I have used AutoAudit-generated triggers to be able to track changes.

The benefit of a trigger is that it is at the table-level and is part of the database schema, regardless of how modifications to the table are requested and regardless of connection mechanism - ODBC/ADO/SP/Agent Job/Access linked table, etc.

One "poor man's" approach to this would be to delete the column from a copy of your database, and then try to find the stored procs which no longer work. There's a code project article that claims to be able to check all stored procs for you, or you may be able to take the concept explained there and build it entirely as a T-SQL script that spits out a T-SQL script that will then error in the appropriate places.

From comment - in that case, replace the Active column with a computed column. E.g.:

ALTER TABLE TableName DROP COLUMN Active
GO
ALTER TABLE TableName ADD Active as CAST(1 as bit)

(Or whatever the appropriate data type is). This will break DML statements whilst SELECTs will still work.

SQL Prompt 5 has a Column Dependencies feature that might work for you.

Check out sys.sql_dependencies. Something like

select object_name(o.object_id)
from sys.sql_dependencies sd
inner join sys.objects o
   on sd.referenced_major_id = o.object_id
inner join sys.columns c
   on o.object_id = c.object_id
   and sd.referenced_minor_id = c.column_id
where o.name = 'your_table_here'
   and c.name = 'your_column_here'

It is probably a good idea to call sp_refreshsqlmodule on all of your stored procedures to make sure that sys.sql_dependencies is up to date. Also, beware that this method won't catch any dynamic sql so if that's a concern you'll need to find another method.

Try this:

SELECT DISTINCT so.name
FROM sys.syscomments sc
INNER JOIN sys.objects so on sc.id=so.object_id
WHERE sc.text LIKE '%colToSearchFor =%'

This assumes there is a space between the column name and the equals sign, so you'll also need to run it without the space or with more spaces/tabs, etc. Still it's better than nothing

Redgate provide a SQL search tool, so you could search your sql code in the database for the column name, which may be an answer. Either that or right click and script your entire database to a file, and the find in that file.

I wouldn't confine it to SPs either. The amount of times I've had this problem and it's been down to a forgotten trigger (grr triggers...)...