0

Is there any way to recognize the SP name as well as all parameter name and type? The regex pattern I used can only recognize the SP name and the last parameter name. I'll be appreciated if anyone can provide an regex that is able to recognize the SP name along with all the parameter names.

Pattern:

.*create procedure\s+.*\.\[(?<spname>\w+)\].*\((.*(?<aname>\@\w+)\s+(?<atype>\w+).*)\).*procedure

Below is the target string:

    IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID('SPNameToBeRecognized') AND OBJECTPROPERTY(id, 'IsProcedure') = 1)
            DROP PROCEDURE SPNameToBeRecognized
    GO

    CREATE PROCEDURE [dbo].[SPNameToBeRecognized] 
    (
            @FirstArg DATETIME = NULL,
            @SecondArg DATETIME = NULL,
            @ThirdArg SMALLINT = 814,
            @FouthArg SMALLMONEY = 10.00
    ) 
/*----------------------------------------------------------------------------------------------------
    Procedure   :
David Faber
  • 12,277
  • 2
  • 29
  • 40
mind1n
  • 1,196
  • 3
  • 15
  • 34
  • Are you using the regex in .NET? You may use something like [this](http://regexstorm.net/tester?p=create+procedure%5cs%2b.*%5c.%5c%5b%28%3f%3cspname%3e%5cw%2b%29%5c%5d%5cs*%5c%28%28%3f%3a%5cs*%28%3f%3caname%3e%40%5cw%2b%29%5cs%2b%28%3f%3catype%3e%5cw%2b%29%5cW.*%29*%5cs*%5c%29&i=CREATE+PROCEDURE+%5bdbo%5d.%5bSPNameToBeRecognized%5d+%0d%0a%28%0d%0a++++++++%40FirstArg+DATETIME+%3d+NULL%2c%0d%0a++++++++%40SecondArg+DATETIME+%3d+NULL%2c%0d%0a++++++++%40ThirdArg+SMALLINT+%3d+814%2c%0d%0a++++++++%40FouthArg+SMALLMONEY+%3d+10.00%0d%0a%29&o=i) there and access each named group capture collection. – Wiktor Stribiżew Nov 14 '18 at 11:43
  • 1
    If this *is* .NET (or you have the option to use it), consider leveraging the [`TSqlParser`](https://learn.microsoft.com/dotnet/api/microsoft.sqlserver.transactsql.scriptdom.tsqlparser) classes and/or SSDT database projects for such tasks. That's vastly more reliable than trying to capture the arcane T-SQL syntax with regexes. – Jeroen Mostert Nov 14 '18 at 14:06
  • 1
    @WiktorStribiżew this still captures only one of the parameter. Is there a way to capture all the parameters along with the SP name? For example, I want the regex to tell me that it matches the as well as 4 and 4 – mind1n Nov 15 '18 at 02:53
  • @mind1n No, it *captures* all of them, but writes into a group only the last occurrence. – Wiktor Stribiżew Nov 15 '18 at 07:35

0 Answers0