CODEWITHSUNDEEP
c#regex
Rishabh Gupta
Rishabh Gupta

Reputation: 136

Faster Regex expression for extracting Stored procedure name

I have this Stored procedure and given a table name(For ex: "userCompanyGrouping_tbl"). I have to extract the Stored Proc names in which the given table name is used.
I am using the regex expression CREATE PROCEDURE\\s*(?<proc_name>.*|\n)(.|\n)*userCompanyGrouping_tbl to extract the stored Procedure from a given below sample .sql file and it is performing very slow.

I have to perform the search on multiple files in a directory of size in GB's. The result comes out to be very slow. I need a faster regex expression to extract Procedure name.

I am using the C# regex Engine.

 USE [BI]
    GO

    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON
    GO

    SET ANSI_PADDING ON
    GO

    IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ActiveUsersRelatedCompanies_prc]') AND type in (N'P', N'PC'))
    DROP PROCEDURE [dbo].[ActiveUsersRelatedCompanies_prc]

    GO

    CREATE PROCEDURE  [dbo].[ActiveUsersRelatedCompanies_prc]  
        @Usercompanyid INT
    AS     
    Begin  

    select * 
    FROM dbo.ActiveUsersRelatedCompanies_tbl (NOLOCK)
    WHERE userCompanyId in (
            select  ucg2.userCompanyId
            from    `userCompanyGrouping_tbl` u
                    inner join userCompanyGrouping_tbl ucg2
                        on isNull(u.subParentCompanyId,u.parentCompanyId) = 
                            (case when u.subParentCompanyId is not null then ucg2.subParentCompanyId 
                                else ucg2.parentCompanyId end)
            where   u.userCompanyId = @userCompanyID
    )       
    order by userCompanyName, userGroup, fullName
    END

Upvotes: 0

Views: 548

Answers (1)

Rishabh Gupta
Rishabh Gupta

Reputation: 136

The following regular expression will be better as written above:

^\s*[^(--)]{0}(CREATE +PROCEDURE|ALTER +PROCEDURE)\s+(?<ParentProcName>(\w|_|\[|\]|\.)*)

The "ParentProcName" will capture the name of Stored Procedure which is either created or altered

Upvotes: 1

Related Questions