I have a pretty extensive SQL Server query setup that performs the following functions. For each RDL report in our system (RDL is stored in a table as binary), I extract the CommandText from each dataset and store it in a table as VARCHAR(MAX). I have a separate table that holds client-created custom database objects (i.e. views, tables, stored procedures, and functions). The goal is for us to scan each report's CommandText for any references to the custom objects we've previously identified. I created a custom function that does the actual CONTAINS scan, and the CommandText is setup for FullText searching.

To be frank, everything works as I want. The issue is that the first time I run the script as a whole (Which includes building the report table to hold the report details, populating said table, creating the fulltext catalog and index, then updating the report table by querying it against the custom search function), the function does not return any results on objects that it should. If I then manually re-run the UPDATE statement that calls the function, it then returns the results as expected.

I do have all the major parts of this split up into batches (i.e. using GO) but it doesn't seem to make a difference. We really want this to be a single script if possible, so I appreciate any pointers or ideas to make sense of this. Scripts below for reference...

This is the part of my query that builds the full-text search index after my custom table that has all the report RDL details in it is populated:

CREATE UNIQUE CLUSTERED INDEX idx_ID ON dbo.tmpPremRptCmdTxt (ID)

    CREATE FULLTEXT CATALOG Prem_tmpPremRptCmdTxt_FTCat 
    CREATE FULLTEXT INDEX ON tmpPremRptCmdTxt
    (  
        DataSet_CommandText            
        Language 1033                 
    )  
        KEY INDEX idx_ID  
        ON Prem_tmpPremRptCmdTxt_FTCat  
        WITH CHANGE_TRACKING OFF

This is the part of the query that calls the function to do the scans and updates the report table if it finds any custom object references:

UPDATE tmpPremRptCmdTxt SET [CustomObjectsUsedInCommandText] = ISNULL(CustRptObjs.[CustomObjectsLocated], '')
FROM tmpPremRptCmdTxt 
LEFT JOIN (
    SELECT 
        ResultSet.ReportID, ResultSet.DatasetID
        , STRING_AGG('[' + CONVERT(VARCHAR(MAX), ResultSet.CustomObjects) + '] (' + ResultSet.ObjectType + ')', '; ') as [CustomObjectsLocated]
    FROM (
        SELECT 
            Rpt.ReportID, Rpt.DatasetID, Cob.ObjectType
            , dbo.[fnPremFTSearch_v2](MAX(Rpt.ID), Rpt.ReportID, Rpt.DatasetID, Cob.ObjectName) as CustomObjects
        FROM tmpPremRptCmdTxt Rpt
        OUTER APPLY         
        (
            SELECT DISTINCT ObjectName, ObjectType 
            FROM tmpPremCustomObjects       
        ) Cob
        WHERE ISNULL(Rpt.DataSet_CommandText, '') <> ''     
        GROUP BY Rpt.ReportName, Rpt.ReportID, Rpt.DatasetID, Cob.ObjectName,ObjectType
        HAVING dbo.[fnPremFTSearch_v2](MAX(Rpt.ID), Rpt.ReportID, Rpt.DatasetID, Cob.ObjectName) IS NOT NULL
    ) ResultSet
    GROUP BY ResultSet.ReportID, ResultSet.DatasetID
) CustRptObjs ON CustRptObjs.ReportID = tmpPremRptCmdTxt.ReportID
    AND CustRptObjs.DatasetID = tmpPremRptCmdTxt.DatasetID

This is the code of the function that I call to do the scan:

CREATE FUNCTION [dbo].[fnPremFTSearch_v2](@ID INT, @ReportID INT, @DatasetID INT, @ObjectName AS VARCHAR(256))
    RETURNS VARCHAR(256)
AS
BEGIN
    DECLARE @ReturnText VARCHAR(256) = ''
    , @SearchText VARCHAR(256) = CONCAT('"', @ObjectName, '"')

    SELECT @ReturnText = @ObjectName 
    FROM tmpPremRptCmdTxt
    WHERE ReportID = @ReportID 
    AND DatasetID = @DatasetID
    AND ID = @ID
    AND CONTAINS(DataSet_CommandText, @SearchText)
    AND DataSet_CommandText LIKE CONCAT('%',@ObjectName, '%') --Weeds out any near matches...i.e. "EmployeeDetail$" would return if CommandText contained "EmployeeDetail" but not "EmployeeDetail$"

    IF (@ReturnText = '')
        SET @ReturnText = NULL

    RETURN @ReturnText
END
GO