Retrieve stored procedure name from string

Question

This stored procedure is in a string variable and I want to retrieve the name.

CREATE PROCEDURE dbo.uspGetEmployeesTest2   
    @LastName nvarchar(50),   
    @FirstName nvarchar(50)   
AS   
    SET NOCOUNT ON;  

    SELECT 
        FirstName, LastName, Department  
    FROM 
        HumanResources.vEmployeeDepartmentHistory  
    WHERE 
        FirstName = @FirstName 
        AND LastName = @LastName  
        AND EndDate IS NULL;  
GO

I would like it to work event if the header looks like that :

CREATE PROCEDURE [dbo].[uspGetEmployeesTest2]

I would like to get : uspGetEmployeesTest2

Is there any library to to this ?

You can use regex https://msdn.microsoft.com/en-us/library/system.text.regularexpressions.regex(v=vs.110).aspx — Serg, Feb 09 '17 at 19:18

Shane Ray · Answer 1 · 2017-02-09T20:01:31.307

0

You should be able to use the following regex CREATE PROCEDURE (?:dbo|\[dbo\])\.\[*(\w+)\]*

See example: https://www.myregextester.com/?r=7e17ea0b

Updated to handle the case described by @JimL: You should be able to use the following regex CREATE PROCEDURE (?:dbo\.|\[dbo\]\.)*\[*([a-zA-Z0-9_ -]+)\]*

See example: https://www.myregextester.com/?r=c4a81cb3

With the above solution you may also want to .Trim() to remove leading or trailing whitespace.

edited Feb 09 '17 at 20:01

answered Feb 09 '17 at 19:21

Shane Ray

1,449
1
13
18

This is a valid SQL Procedure name: [My Procedure] This may help some: http://stackoverflow.com/questions/30151800/regular-expression-for-validating-sql-server-table-name – Jim L Feb 09 '17 at 19:48

Retrieve stored procedure name from string

1 Answers1