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

Another option

WITH cteBase AS (
  SELECT p.*, x.pos, x.Namex
       , rn = ROW_NUMBER()  OVER (PARTITION BY p.PatID ORDER BY...

Thanks DesNorton,

Inner join will give me ~40 odd GB used by various table. The intent is to find allocation units that are not part of actual usage hence the...