从存储的过程定义中提取字符串 [英] extract a string from a stored proc definition

问题描述

我需要检查几个从远程服务器提取数据的存储过程使用的库.

I need to check the library used by several stored procs that extract data from a remote server.

我有(在 SO 帮助下，请参阅 SO 21708681) 构建了以下代码:

I have (with SO help, see SO 21708681) built the below code:

DECLARE @tProcs TABLE
    (
        procID int IDENTITY,
        procObjectID nvarchar(100),
        procName nvarchar(100)
    );

insert into @tProcs     
SELECT object_id, name 
    FROM sys.objects 
        WHERE  type in (N'P', N'PC') and name like '%_Extract'

declare @countProcs int, @I int=0

select @countProcs=COUNT(*) from @tProcs

while @I<@countProcs
    Begin
        declare @source_code nvarchar(max)
        declare @objectID nvarchar(50)
        declare @proc_Name nvarchar(200)

        select @objectID=procObjectID from @tProcs where procID=@I
        select @proc_Name=procName from @tProcs where procID=@I

        select @source_code = definition
            from sys.sql_modules
            where object_id = @objectID 

        SELECT PATINDEX('BOCTEST.%', @proc_Name) as Pos, @proc_Name 

              -- or SELECT charindex(@source_code, '%BOCTEST%') 

        set @I=@I+1
    End

在每个目标存储过程中有这样一行:

Inside each of the target stored procs there is a line like this:

DECLARE YP040P_cursor CURSOR FOR SELECT * FROM BOCTEST.S653C36C.LIVEBOC_A.YP040P

我需要知道每个存储过程的LIVEBOC_A"部分(可以是LIVEBOC_A"或LIVEBOC_B")

I need to know for each of the stored procs the part 'LIVEBOC_A' (which can either be 'LIVEBOC_A' or LIVEBOC_B)

我尝试使用 PATINDEX 和 CHARINDEX 从 sysmodules 的定义中获取该字符串的起始 opf 位置，但我得到的只是零或字符串或二进制数据的错误已截断.

I tried to use PATINDEX and CHARINDEX to get the location of the start opf that string in the definition from sysmodules but all I get back is either zero or an error that string or binary data would be truncated.

推荐答案

try

SELECT 
    name, 
    table_name = CASE WHEN OBJECT_DEFINITION(OBJECT_ID) LIKE '%BOCTEST.S653C36C.LIVEBOC_A.YP040P%' THEN 'LIVEBOC_A'
                      WHEN OBJECT_DEFINITION(OBJECT_ID) LIKE '%BOCTEST.S653C36C.LIVEBOC_B.YP040P%' THEN 'LIVEBOC_B' END
FROM sys.objects o
WHERE o.[type] IN ('P', 'PC')
AND name like '%_Extract'

这篇关于从存储的过程定义中提取字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！