A TVP is a table variable.  Variables do not have any statistics.  Historically SQL always assumed that a table variable contained exactly 1 record.  In SQL 2019, this was changed...

To create sample data for people to assist you with ...

CREATE TABLE #TsetData (RBDCTL int, RBDTTC int, RBDTYR  int);

INSERT INTO #TsetData ( RBDCTL, RBDTTC, RBDTYR )
VALUES...

OK, try it as a basic aggregate

SELECT    RBDCTL
        , RBDTTC
       ...

Try using a window function

SELECT    RBDCTL
        , RBDTTC
        , MaxYear...

Since you have not provided an expected output, hopefully this will get you started

DECLARE @RefTable table (
    ID      ...

This is a great book on indexing

Expert Performance Indexing in SQL Server, Second Edition

I use the following script to search for dependencies

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

DECLARE 
    @ObjectSchema SYSNAME = '' /* You can limit the...

It...

CharIndex only uses a single character. For a...

Something like this

CREATE TABLE #MyTable (ID int, DATA NVARCHAR(MAX))

INSERT INTO #MyTable values (1, 'ID:12345, Name: Chris , Tiger, Sal:3000, City: NYC')
      ...

CharIndex only uses a single character.

For a group of characters,...

Rather have your UI pass the CustomerID from it's own context when passing the note to be saved.  Shredding free text is expensive and will definitely come back to bite...

I also put something like that together where it would look like this

where ([definition] like '%INTO [tableName]%' or ([definition] like '%update [tableName]%')

That got me the closest...

My previous answer does not cater for scenarios where ALL Name values are "NONE".

Updated code - Please test thoroughly.

WITH cteBase AS (
  SELECT p.PatID, x.pos, x.Namex
...

For more info on using the CROSS APPLY method, please read this article

https://www.sqlservercentral.com/articles/an-alternative-better-method-to-unpivot-sql-spackle