;WITH cte_test_data AS (
    SELECT * FROM ( VALUES
        ('00000S123', 'US', 'ITALY'),
        ('00000BH01', 'UK', ''),
   ...

I agree with the other critiques.  As to "the guy", he's 100% right, and he should already run, not walk, to another contractor, without delay!

1NF is trivial and...

Never use ISNULL() in a JOIN (or WHERE) is a good rule.

To avoid issues with NULL, perhaps you could use empty string as no suborder rather than NULL?

I suggest using MINUTE rather than HOUR as the difference between local and UTC.  Yes, some places actually do offset by not-an-even-hour, so, to be safe, use minutes.

set @counter = @counter + 1 /* increment counter */  /* I see this or slight variations of it all the time */
--------------------...
/* not quite...

The easiest and cleanest way to do this would be to create stored proc in the master db, start the name with "sp_", mark it as a system object, and...

SELECT tn.*
FROM (
    SELECT NUMBER
    FROM dbo.table_name
    GROUP BY NUMBER
    HAVING COUNT(*) > 1
) AS NUMBER_DUPS
INNER...

In terms of potential lost performance and wasted resources, this is probably the single dumbest thing:

CREATE TABLE dbo.every_table_created
(
    id int IDENTITY(1, 1)...

Perhaps a 3rd option.  Rather than pulling the data from all instances, have each instance gather that info on itself and push it to a central location.