We have source control in Visual Studio Database Projects (SSDT). To make cross database references, we utilize something like this below in Project References.
We need Project references, since Otherdatabase, can choose between two options at our company. Most of the time, its 90% Database A, and 10% Database B.
This is a headache for developers, to write code with $ and brackets . Is there any work around for this? Eg, Say I write my code in DatabaseA default,
from [$(OtherDatabase)].dbo.PurchaseOrder from DatabaseA.dbo.PurchaseOrder
Is there a method to have Multiple publish profile scripts, Can I write a publish profile script to find all references to Database A, and replace with Database B? Maybe like a simple Ctrl-Find and Replace? How do I incorporate this kind of command, batchfile, stored procedure or Powershell in publish profiles?
Searching for an efficient way; I may use this answer below, however DatabaseA needs to reside in same server, and then I would drop and recreate which may not be optimal.
Find and replace database name in views and stored procedures
Aware of synonyms trying to avoid this route, and conduct find/replace