If there's a way of doing this without regular expressions, that's great. If there isn't, here's what I've got so far:
I've written a simple CLR user-defined function (which as you can see I've called
CLR_RegExMatch) that performs regex matches on a supplied string. I use it to search for patterns inside stored procedures, triggers, functions etc.
Here's an example of its use - searching for inserts into a table called ExampleTable:
SELECT O.name, O.type_desc FROM SYS.OBJECTS O INNER JOIN SYS.SQL_MODULES M ON M.object_id = O.object_id AND dbo.CLR_RegExMatch('INSERT\s+(INTO\s+)?ExampleTable\b', M.definition) = 1
The issue I've got is that I can't come up with a regex pattern to find all routines that delete rows from a given table. Obviously I could substitute the following for the last line in the previous example:
AND dbo.CLR_RegExMatch('DELETE\s+(FROM\s+)?ExampleTable\b', M.definition) = 1
and that gets me part of the way there. However it wouldn't pick up the following:
DELETE T1 FROM ExampleTable T1 INNER JOIN AnotherTable T2 ON T2.ParentId = T1.Id
So what I'm looking for is either a regex pattern that will match deletes as above, or alternatively a different way of going about this.
N.B. The reason that I'm querying the
SYS.SQL_MODULESinstead of the
INFORMATION_SCHEMA.ROUTINESis that the latter only contains the first 4000 characters a routine definition, whereas the former contains the full text.
Have a look at the FREE Red-Gate tool called SQL Search which does this - it searches your entire database for any kind of string(s).
It's a great must-have tool for any DBA or database developer - did I already mention it's absolutely FREE to use for any kind of use??
It will not tell you which procedures actually delete something from a table - but it will very easily and nicely find all procedures which reference that table in any way. Look at those and find those you need!