Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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... 
share|improve this question
add comment

1 Answer

up vote 5 down vote accepted

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.

share|improve this answer
    
Not bad, but not great. One stored proc may have 10 different updates on Transaction, and many stored procs update Transaction. I was thinking by now someone must have a more exact utility. We use the free SQLSearch plugin from RedGate, but it's not exact enough either. –  NealWalters Jan 24 at 20:15
    
I think the other issue with your idea is noise words, "update" may occur in a comment for example. So might "transaction" and "terminalId". Is m.definition the entire stored proc (not a line from the stored proc, right)? I know some people use semicolon at the end of sql statements; something like that would for sure help with parsing multi-line statements. –  NealWalters Jan 24 at 20:19
    
@NealWalters well please let me know if you find anything better. I don't think a tool exists for this precisely because it's hard. –  Aaron Bertrand Jan 24 at 20:19
    
@NealWalters yes, like I said, could be embedded in a comment or a string. And yes, m.definition is the entire definition (unlike syscomments which has multiple rows, or INFORMATION_SCHEMA - which is truncated). –  Aaron Bertrand Jan 24 at 20:20
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.