SQL Server – Searching All Stored Procedures for Specific Column Update

sql-server-2008-r2stored-procedures

I'm looking at an audit table created by a trigger, and we were smart enough to save the SUSER_NAME(); we have a column called LastUpdateRef, but not all old code has been updated to use it (it's a unique code to help trace updates back to the code that caused them).

So I know someone is updating, for example, TerminalId in the Transaction table. How would I answer this?

Show me all the stored procs that have an update statement on the Transaction table that explicitly change the TerminalId

Seems like this would be beyond any RegEx that would be easy to write.

Some updates statements are in this format, where the table name is after FROM: 

update a
set 
    ArrivalDate = @ArrivalDateLocalTime,
    LastUpdate = SYSDATETIME(),
    LastUpdateRef = 'REFGFI168',
    LastUpdateBy = @UserID
from Flight a where etc...

Best Answer

Do you have many procedures that getting the rough net and then eliminating any false positives manually would be extremely objectionable?

SELECT s.name, p.name, m.definition
  FROM sys.procedures AS p
  INNER JOIN sys.sql_modules AS m
  ON p.[object_id] = m.[object_id]
  INNER JOIN sys.schemas AS s
  ON p.[schema_id] = s.[schema_id]
WHERE LOWER(m.definition) LIKE '%update%transaction%terminalid%'
   OR LOWER(m.definition) LIKE '%update%terminalid%transaction%'
   OR LOWER(m.definition) LIKE '%with%transaction%update%terminalid%';

The last case to cover this form:

;WITH x AS (SELECT ... FROM dbo.Transaction)
UPDATE x SET TerminalID = ... FROM x INNER JOIN ...;

I don't know how much more reliable a "better" RegEx could be at identifying only those that truly update that column, and never identifying a false positive. In fact any real update statement that matched the above expressions (or any RegEx expression you would be likely to cook up initially) could be embedded in a comment or a string.

I'd suggest start simple. If the above yields way too many false positives then get more precise.

Related Question