How to fix error "Line 25: The statement terminated. The maximum recursion 100 has been exhausted before statement completion."

Question

I added OPTION (MAXRECURSION 1000) at the very bottom but it was not enough. My query:

Use DatabaseName;

DECLARE @SOURCE_TABLE_NAME  NVARCHAR(200) = 'WorkOrderVersion'
DECLARE @SOURCE_SCHEMA_NAME NVARCHAR(200) = 'workorder'
DECLARE @TARGET_TABLE_NAME  NVARCHAR(200) = 'WorkOrder'
DECLARE @TARGET_SCHEMA_NAME NVARCHAR(200) = 'workorder'

-- holds the navigation of all foreign-key routes from the source table onwards.
--------------------------------------------------------------------------------
DECLARE @RESULTS AS TABLE
(
    [ROW_ID]            [UNIQUEIDENTIFIER] NOT NULL PRIMARY KEY,
    [PARENT_ROW]        [UNIQUEIDENTIFIER]  NULL,
    [CHILD_SCHEMA]      [NVARCHAR](250)     NULL,
    [CHILD_TABLE]       [NVARCHAR](250)     NULL,
    [CHILD_COLUMN]      [NVARCHAR](250)     NULL,
    [PARENT_SCHEMA]     [NVARCHAR](250)     NULL,
    [PARENT_TABLE]      [NVARCHAR](250)     NULL,
    [PARENT_COLUMN]     [NVARCHAR](250)     NULL,
    [CONSTRAINT_NAME]   [NVARCHAR](250)     NULL,
    [PATH]              [NVARCHAR](600)     NULL,
    [DEPTH]             [INT]               NOT NULL
)

;WITH [PARENT_RELATIONS]
        AS (
                -- return specific process
                --------------------------
                SELECT  NEWID()                                                                             AS [ROW_ID]             ,
                        CAST(NULL AS UNIQUEIDENTIFIER)                                                      AS [PARENT_ROW]         ,
                        [CHILD].[TABLE_SCHEMA]                                                              AS [CHILD_SCHEMA]       ,
                        [CHILD].[TABLE_NAME]                                                                AS [CHILD_TABLE]        ,
                        [CHILD_COLUMN].[COLUMN_NAME]                                                        AS [CHILD_COLUMN]       ,
                        [PARENT].[TABLE_SCHEMA]                                                             AS [PARENT_SCHEMA]      ,
                        [PARENT].[TABLE_NAME]                                                               AS [PARENT_TABLE]       ,
                        [PARENT_COLUMN].[COLUMN_NAME]                                                       AS [PARENT_COLUMN]      ,
                        [CONTRAINT].[CONSTRAINT_NAME]                                                       AS [CONSTRAINT_NAME]    ,
                        CAST(UPPER(
                            '[' + [CHILD].[TABLE_SCHEMA]    + '].[' + [CHILD].[TABLE_NAME]  + '].[' + [CHILD_COLUMN].[COLUMN_NAME]  + '] = ' +
                            '[' + [PARENT].[TABLE_SCHEMA]   + '].[' + [PARENT].[TABLE_NAME] + '].[' + [PARENT_COLUMN].[COLUMN_NAME] + ']') AS [varchar](600))           AS [PATH]   ,
                        0                                                                                   AS [DEPTH]

                FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS [CONTRAINT]

                    INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS [CHILD]
                    ON [CONTRAINT].[CONSTRAINT_NAME] = [CHILD].[CONSTRAINT_NAME]

                    INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS [PARENT]
                    ON  [PARENT].[CONSTRAINT_NAME] = [CONTRAINT].[UNIQUE_CONSTRAINT_NAME]
                    AND [PARENT].[CONSTRAINT_TYPE] = 'PRIMARY KEY'

                    INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE [CHILD_COLUMN]
                    ON [CONTRAINT].[CONSTRAINT_NAME] = [CHILD_COLUMN].[CONSTRAINT_NAME]

                    INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS [PARENT_COLUMN]
                    ON  [PARENT_COLUMN].[CONSTRAINT_NAME]   = [PARENT].[CONSTRAINT_NAME]
                    AND [PARENT_COLUMN].[TABLE_NAME]        = [PARENT].[TABLE_NAME]

                WHERE   [CHILD].[TABLE_NAME]    = @SOURCE_TABLE_NAME
                AND     [CHILD].[TABLE_SCHEMA]  = @SOURCE_SCHEMA_NAME

            UNION ALL

                -- return related relationships
                --------------------------
                SELECT  NEWID()                                                                             AS [ROW_ID]             ,
                        [PARENT_RELATIONS].[ROW_ID]                                                         AS [PARENT_ROW]         ,
                        [CHILD].[TABLE_SCHEMA]                                                              AS [CHILD_SCHEMA]       ,
                        [CHILD].[TABLE_NAME]                                                                AS [CHILD_TABLE]        ,
                        [CHILD_COLUMN].[COLUMN_NAME]                                                        AS [CHILD_COLUMN]       ,
                        [PARENT].[TABLE_SCHEMA]                                                             AS [PARENT_SCHEMA]      ,
                        [PARENT].[TABLE_NAME]                                                               AS [PARENT_TABLE]       ,
                        [PARENT_COLUMN].[COLUMN_NAME]                                                       AS [PARENT_COLUMN]      ,
                        [CONTRAINT].[CONSTRAINT_NAME]                                                       AS [CONSTRAINT_NAME]    ,
                        CAST([PARENT_RELATIONS].[PATH] + UPPER(' -->> ' +
                            '[' + [CHILD].[TABLE_SCHEMA]    + '].[' + [CHILD].[TABLE_NAME]  + '].[' + [CHILD_COLUMN].[COLUMN_NAME]  + '] = ' +
                            '[' + [PARENT].[TABLE_SCHEMA]   + '].[' + [PARENT].[TABLE_NAME] + '].[' + [PARENT_COLUMN].[COLUMN_NAME] + ']') AS [varchar](600))
                                                                            AS [PATH]   ,
                        [PARENT_RELATIONS].[DEPTH] + 1                                                      AS [DEPTH]

                FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS [CONTRAINT]

                    INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS [CHILD]
                    ON [CONTRAINT].[CONSTRAINT_NAME] = [CHILD].[CONSTRAINT_NAME]

                    INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS [PARENT]
                    ON  [PARENT].[CONSTRAINT_NAME] = [CONTRAINT].[UNIQUE_CONSTRAINT_NAME]
                    AND [PARENT].[CONSTRAINT_TYPE] = 'PRIMARY KEY'

                    INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE [CHILD_COLUMN]
                    ON [CONTRAINT].[CONSTRAINT_NAME] = [CHILD_COLUMN].[CONSTRAINT_NAME]

                    INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS [PARENT_COLUMN]
                    ON  [PARENT_COLUMN].[CONSTRAINT_NAME]   = [PARENT].[CONSTRAINT_NAME]
                    AND [PARENT_COLUMN].[TABLE_NAME]        = [PARENT].[TABLE_NAME]

                        INNER JOIN [PARENT_RELATIONS] AS [PARENT_RELATIONS]
                        ON  [PARENT_RELATIONS].[PARENT_TABLE]       = [CHILD].[TABLE_NAME]
                        AND [PARENT_RELATIONS].[PARENT_SCHEMA]      = [CHILD].[TABLE_SCHEMA]

            )

            -- inserts all possible results into local storage for iteration
            ----------------------------------------------------------------
            INSERT INTO @RESULTS SELECT * FROM [PARENT_RELATIONS];

;WITH [KNOWN_PATH]
AS (
            -- return the optimal root target row information
        -------------------------------------------------
        SELECT      [FILTER].[PARENT_ROW]       as [PARENT_ROW]     ,
                    [FILTER].[CHILD_SCHEMA]     as [CHILD_SCHEMA]   ,
                    [FILTER].[CHILD_TABLE]      as [CHILD_TABLE]    ,
                    [FILTER].[CHILD_COLUMN]     as [CHILD_COLUMN]   ,
                    [FILTER].[PARENT_SCHEMA]    as [PARENT_SCHEMA]  ,
                    [FILTER].[PARENT_TABLE]     as [PARENT_TABLE]   ,
                    [FILTER].[PARENT_COLUMN]    as [PARENT_COLUMN]  ,
                    [FILTER].[DEPTH]            as [DEPTH]          ,
                    [FILTER].[PATH]             as [PATH]
        FROM        (
                        SELECT      ROW_NUMBER() OVER(ORDER BY [DEPTH]) AS [OPTIMAL],
                                    [RESULTS].[PARENT_ROW]          as [PARENT_ROW]     ,
                                    [RESULTS].[CHILD_SCHEMA]        as [CHILD_SCHEMA]   ,
                                    [RESULTS].[CHILD_TABLE]         as [CHILD_TABLE]    ,
                                    [RESULTS].[CHILD_COLUMN]        as [CHILD_COLUMN]   ,
                                    [RESULTS].[PARENT_SCHEMA]       as [PARENT_SCHEMA]  ,
                                    [RESULTS].[PARENT_TABLE]        as [PARENT_TABLE]   ,
                                    [RESULTS].[PARENT_COLUMN]       as [PARENT_COLUMN]  ,
                                    [RESULTS].[DEPTH]               as [DEPTH]          ,
                                    [RESULTS].[PATH]                as [PATH]

                        FROM    @RESULTS AS [RESULTS]
                        WHERE   [RESULTS].[PARENT_TABLE]    = @TARGET_TABLE_NAME
                        AND     [RESULTS].[PARENT_SCHEMA]   = @TARGET_SCHEMA_NAME
                    ) AS [FILTER]
        WHERE   [FILTER].[OPTIMAL] = 1


        UNION ALL

            -- return the parent of the target up the path
            -------------------------------------------
            SELECT      [RESULTS].[PARENT_ROW]          as [PARENT_ROW]     ,
                        [RESULTS].[CHILD_SCHEMA]        as [CHILD_SCHEMA]   ,
                        [RESULTS].[CHILD_TABLE]         as [CHILD_TABLE]    ,
                        [RESULTS].[CHILD_COLUMN]        as [CHILD_COLUMN]   ,
                        [RESULTS].[PARENT_SCHEMA]       as [PARENT_SCHEMA]  ,
                        [RESULTS].[PARENT_TABLE]        as [PARENT_TABLE]   ,
                        [RESULTS].[PARENT_COLUMN]       as [PARENT_COLUMN]  ,
                        [RESULTS].[DEPTH]               as [DEPTH]          ,
                        [RESULTS].[PATH]                as [PATH]

            FROM    @RESULTS AS [RESULTS]

                INNER JOIN [KNOWN_PATH]
                ON [RESULTS].[ROW_ID] = [KNOWN_PATH].[PARENT_ROW]
        )

        -- return the correct path from one-table to another
        ----------------------------------------------------
        SELECT  [DEPTH]         ,
                [PATH]          ,
                [CHILD_SCHEMA]  ,
                [CHILD_TABLE]   ,
                [CHILD_COLUMN]  ,
                [PARENT_SCHEMA] ,
                [PARENT_TABLE]  ,
                [PARENT_COLUMN]
        FROM [KNOWN_PATH] ORDER BY [DEPTH] ASC
        OPTION (MAXRECURSION 1000);