You'll want to add ERROR_MESSAGE() at least to your CATCH code.  That will give you the specific error msg SQL issued.  You should probably go ahead and add ERROR_LINE(), although...

See if this works for you:

;WITH cte_raw_counts AS (
    SELECT COUNT(*) AS total_company_count,
        SUM(CASE WHEN rr.[company_name] =...

I think it would be more accurate and far less work to rely on the existing constraints.

You should be able to use a BEGIN TRANSACTION and a TRY/CATCH block to...

I'd do an explicit sp_recompile on all the tables affected by the renames, just to be safe.  Renaming is great, but it's a short-cut method that's not fully "recognized" by...

That shouldn't be possible. Per Logical...

I think creating a table and using EXCEPT / INTERSECT would perform better than a long string of ORs.  Either would also take care of NULL values for you.  So,...

WHERE PS.popup_gid = 5 OR
    (PS.popup_gid = 2 AND (T.record_Key_4 = '1' OR T.Record_Key_5 NOT IN ('I', 'X')))

Just CAST() the column as a date, that gives you the most flexibility with the format of the column:

CONVERT(varchar(5), CAST(alertQueue.[Pickup_Date] AS date), 101) AS PickupDate

Be sure to run the Data Migration Assistant on every user db.  Don't assume one db is "just like" another one.

If the db is open and usable after it migrates,...

Rats, I'd hoped it would work too.

If it's keyed lookups, the size of the underlying table won't matter, SQL would still do the lookup, I understand that.

Here's the general structure.  You can gen this out from the sys.columns table so you don't have to write out the code by hand.

SELECT 
  ...

Yes, there will be some limited page splits.  But the reads against the table will be vastly more efficient, even with the splits.

It's certainly possible that [TimeDayId] then [Material_Werksdaten_key] would...

Add an outer query, something like this:

SELECT *, [Gross_Revenue] -  [Debits_and_Credits_Cost] AS NetRev
FROM (
    SELECT Count(*) AS 'Count of Tranactions',
   ...

You should cluster the table on:

( Material_Werksdaten_key, TimeDayId )

Your join is very confusing.  You need to specify the TimeDayId(s) directly, not use a function on it.

Is TimeDayId one value per...

select Id, CompletedDate, Code 
from (
    select *, row_number() over(partition by id order by completeddate desc) AS row_num
    from #temp_dat1
) as...