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
Note: Only a member of this blog may post a comment.