2

I've got a field in a table that changed value unexpectedly. Clearly, a stored procedure caused that (I've already searched the few 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) ?

Brann
  • 31,689
  • 32
  • 113
  • 162
  • 3
    "Clearly, a stored procedure caused that" - there are many clichés about assumptions, most of which would probably get my comment flagged. – Damien_The_Unbeliever Feb 24 '11 at 09:42
  • You're right. I've added more information on this (basically, I've already searched the few triggers we're using, and no logins have write access on any table) – Brann Feb 24 '11 at 10:03
  • What you can do is restrict to the intersection of SQL modules containing "UPDATE", "TableName" and "Active". This would probably narrow it about as much as you can. – Cade Roux Feb 24 '11 at 10:26

6 Answers6

2

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.

Cade Roux
  • 88,164
  • 40
  • 182
  • 265
  • Indeed, no logins have write access to the table, and we don't use triggers (I've updated my initial question to clarify this). I agree some triggers would help this kind of debug process, but this is not going to work to track bugs that have already occurred. – Brann Feb 24 '11 at 09:58
  • and the thing with "searching all procedures" is that I've got hundreds of procedures depending on this table. Most of this procedures have queries joining this table on the Active=1 field. – Brann Feb 24 '11 at 10:00
  • @Brann No easy way. If your SPs have a naming convention, you only need to check ones which are updates. What I like to see is basic CRUD SPs for each table, reporting SPs which combine many tables (but never INSERT/UPDATE) and then some batch or business/transaction SPs which do bulk operations or multi-step operations (rolling over inventory or something like that). Obviously, if this is happening in a place where it shouldn't, all the conventions in the world aren't really going to help. – Cade Roux Feb 24 '11 at 10:24
  • @Brann A thing to help with the many Active = 1 (in hindsight) filtered joins would be wrap that in an ActiveWhatever view and always join to that instead. But that kind of refactoring takes a while. – Cade Roux Feb 24 '11 at 10:24
2

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.

Damien_The_Unbeliever
  • 234,701
  • 27
  • 340
  • 448
  • the ting is that this would break all the procedures relying on a READ access to my column. I'm trying to find only the procedures requiring a write access. – Brann Feb 24 '11 at 10:32
  • using computed columns is a great idea ! +1 from me – Brann Feb 24 '11 at 10:46
1

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

Trent
  • 1,089
  • 3
  • 12
  • 24
1

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.

Ben Thul
  • 31,080
  • 4
  • 45
  • 68
  • This works great in SQL Server 2008! You do have to qualify that first reference to object_id though. – catfood May 23 '12 at 22:15
1

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

David Atkinson
  • 5,759
  • 2
  • 28
  • 35
0

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...)...

Paddy
  • 33,309
  • 15
  • 79
  • 114