extract a string from a stored proc definition

Question

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

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

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

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.

Answer 1

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'