How to Find All Stored Procedures That Delete Rows from a Specific Table

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 definition column of SYS.SQL_MODULES instead of the ROUTINE_DEFINITION column of INFORMATION_SCHEMA.ROUTINES is 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!

Related Articles