Saturday, February 16, 2013

Regex to match executing a stored procedure within a stored procedure

Unless you're working with a custom string format, which requires you writing a regular expression, I suppose (suggest?) you'll look it up online, right?

If you need a Regex to match an IP, MAC or an E-mail, would you spend time writing it? Chances are that you might leave room for false positives and/or false negatives, unless you really test it. That's why it's common to look it up online.

I needed to take a list of proc names and parse thousands of create procedure scripts, looking up if anything from the input list was used (executed) from those procs. A procedure executing another procedure.

This kind of call can be made in several different ways. It might have or not the database name (cross db call), server name (linked server), it might have or not the EXEC/EXECUTE keyword. It might have or not brackets or set the result to a variable.

Since I looked it up and couldn't find it, I wrote it.
In case you need it, here you go:

^\s*((exec(ute)?)\s+)?(@\w+\s+=\s+)?((\[?\w+\]?\.{1,2}){1,3})?\[?p_storedProcedure\]?\s.*$

Note that if you use SQL Server with default collation, proc names are case insensitive. So make sure you let your Regex engine know it should ignore cases! Otherwise you'll have to mind the case of the EXEC/EXECUTE keywords anyway.

Also note that cross database and linked server calls will also match. Some examples of valid proc calls (that will match against the Regex) are:

p_storedProcedure -- comments
p_storedProcedure @id, @anotherParam
[p_storedProcedure] @id, @anotherParam
EXEC p_storedProcedure @id, @anotherParam
EXECUTE p_storedProcedure @id, @anotherParam
EXEC p_storedProcedure
EXEC p_storedProcedure -- comments
EXEC [p_storedProcedure] @id, @anotherParam
EXEC dbo.p_storedProcedure @id, @anotherParam
EXEC dbo.[p_storedProcedure] @id, @anotherParam
EXEC anySchema.[p_storedProcedure] @id, @anotherParam
EXECUTE dbo.[p_storedProcedure] @id, @anotherParam
EXEC [dbo].[p_storedProcedure] @id, @anotherParam
EXEC [dbo].p_storedProcedure @id, @anotherParam
EXEC DBTEST..p_storedProcedure @id, @anotherParam
EXEC DBTEST..[p_storedProcedure]
EXEC DBTEST.dbo.p_storedProcedure @id, @anotherParam
EXEC [DBTEST]..[p_storedProcedure] @id, @anotherParam
EXEC [DBTEST]..p_storedProcedure @id, @anotherParam
EXEC [DBTEST].[dbo].[p_storedProcedure] @id, @anotherParam
EXEC [LINKEDDATABASE].[DBTEST].[dbo].[p_storedProcedure] @id, @anotherParam
EXEC LINKEDDATABASE.DBTEST.dbo.p_storedProcedure @id, @anotherParam
EXEC LINKEDDATABASE.DBTEST..p_storedProcedure @id, @anotherParam
EXEC @paramName = [dbo].[p_storedProcedure] @id, @anotherParam
EXEC @paramName = dbo.p_storedProcedure
EXEC @paramName = p_storedProcedure @id, @anotherParam
EXECUTE @paramName = p_storedProcedure @id, @anotherParam
EXECUTE    @paramName     =     p_storedProcedure     @id, @anotherParam

No comments:

Post a Comment