I have this TVF in DB1:
ALTER FUNCTION [dbo].[fn_replica_valid_date]
WHEN (rldx_audit_key = 26) THEN DATEADD(DAY,3,CAST(extract_date AS DATE))
ELSE DATEADD(DAY,2,CAST(extract_date AS DATE))
END AS replica_valid_date
WHERE rldx_audit_key = @rldx_audit_key
The identical code needs to run in DB2 (and DB3 and DB4 and ...).
Synonyms to the rescue. So I created a synonym in DB2 pointing to the TVF in DB1.
Both databases contain the table dbo.RLDX_AUDIT_TRAIL, with different data.
However, when I execute the code via the synonym in DB2, I retrieve the data from DB1. For example, I provide an rldx_audit_key parameter that is in the DB1 table, but not in the DB2 table and so should return NULL.
Is this just how synonyms work? I assume yes (bummer), but please confirm.
If so, I guess I need to cut-and-paste code across the databases, and all the maintenance headaches that introduces.