Do a data design first -- that is, a logical design -- before you do a database design (a physical design).  Otherwise you almost always end up with a poor...

Interesting q.  I don't believe that info is stored in any system table, I think SQL generates it (only) when the function executes.

To get the column names, you...

It looks like the FULL backup is from after the DIFF was taken.  You'd need to use the full from before the diff.

CREATE TRIGGER tr_DWH_HRxsins
ON dbo.DWH_HRxsDEL
AFTER INSERT
AS
SET NOCOUNT ON;
DECLARE @record varchar(1000)
SELECT @record = STUFF((
  SELECT '. Record RXNO ' + CAST(i.id...

Maybe something like this (?):

CREATE TABLE #templates_update (
  stringtemplate varchar(max) NOT NULL,
  milestone varchar(10) NOT NULL,
  language varchar(10) NOT NULL
  )

Its owner name is likely 'dbo'.  The actual owner of the db has the user name 'dbo', even though the login name is different.  Remember, SQL doesn't require the login...

If FK constraints have been defined, you can use views:
sys.foreign_keys and
sys.foreign_key_columns
to check for invalid / missing FK values.  If you're not worried about checking NULL values, I...

I like to code to handle the case where there's no '\' in the data, to avoid the dreaded "Invalid length passed to RIGHT function."

DECLARE...

Full DDL for the table would really help.  But for now, try these indexes and then re-examine the stats in a week or two.  Index tuning is definitely an iterative...

Sorry; in this statement:
...
SET @table_name_pattern = '%' --<<--put your preferred table name / name LIKE pattern here
...

Please specify the table name / name pattern in the code below and run it, then post the two separate result sets.  (Results in .xlsx format is great, but don't...

You should determine the best clustered index for every table (the only exceptions could be a bulk load or other staging table).  Note that typically this is not an identity column....

To add a little more detail to my first recommendation, I think you should be able to do this:

1) Update any rows where link1 > link2 to swap...

Yeah, OBJECT_ID defaults to the current db only.  Easiest is to adjust the code to shift context to each db, something like this:

DECLARE IDX CURSOR...