如果在文件中使用了特定的表，则使用regex从文件中提取存储过程的名称

本文关键字：文件 regex 提取 存储过程 如果 | 更新日期: 2023-09-27 18:09:09

我有一个SQL文件，其中有多个存储过程。我已经获得了一个表名，需要找出使用该表的存储过程。如果用c#给出答案就好了。

。给定两个过程，表名为"ucg2"。userCompanyId"，我们需要告诉哪个过程使用它。

CREATE PROCEDURE  [dbo].[ActiveUsersAM_prc]    
--ActiveUsers_getdata_prc    
    @Usercompanyid varchar(max)    
AS     
Begin    

IF OBJECT_ID('tempdb..#ActiveUserCompany') IS NOT NULL  
DROP TABLE #ActiveUserCompany  
CREATE TABLE #ActiveUserCompany  
(userCompanyId INT)  
INSERT INTO #ActiveUserCompany  
SELECT val FROM dbautil.dbo.Split_fn(@userCompanyID,',')  

CREATE CLUSTERED INDEX ix_usercompanyId ON #ActiveUserCompany(userCompanyId)  
SELECT * FROM dbo.ActiveUsersAMCache_tbl (nolock)
WHERE userCompanyId IN (SELECT userCompanyId FROM #ActiveUserCompany (nolock))
END

和

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

如果在文件中使用了特定的表，则使用regex从文件中提取存储过程的名称

这里有一些简化，但它应该是一个良好的开端。

// Read content
string content = File.ReadAllText(filename);
// Separate procedures from each other
// (you might have to use "ToUpper()" before)
string[] procs = content.Split(new string[] { "CREATE PROCEDURE" }, StringSplitOptions.None);
// Check if one of them contains the table name
string table = "ucg2.userCompanyId";
foreach (string proc in procs)
{
    // If it does, print the first line (which holds the name of the stored procedure
    // (Using regex here might be necessary, depending on the source)
    if (proc.Contains(table))
    {
        Console.WriteLine(proc.Split(new string[] { "'r'n" }, StringSplitOptions.None)[0]);
    }
}
相关文章：