Sql-server – SQL Visual Studio Find and Replace in Publish Profile

powershellsql serversql-server-2016ssdtvisual studio

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

Best Answer

When writing cross-database code, I prefer to avoid three-part name references. The variability of the $(OtherDatabase) that you describe is the precise reason why I avoid three-part names. Someday, DatabaseA will be renamed to DatabaseC and have to update a bunch of references in code. In your case, you can just do a new build, but it's still a pain.

Instead, create synonyms in the project for each cross-database reference. In the synonym, you will use the SSDT project reference variable once per object:

CREATE SYNONYM dbo.PurchaseOrder FOR [$(OtherDatabase)].dbo.PurchaseOrder;

Then, every time you reference the cross-database object, you only need to use the two-part name. Developers should use neither the [$(OtherDatabase)] nor a placeholder DatabaseA reference for find-and-replace.

FROM dbo.PurchaseOrder

This has the added benefit that all of the code will always be identical across different iterations of the database. The only object types that will not match will be synonyms. Rather than inspecting code, you can simply query the sys.synonyms DMV to determine where dbo.PurchaseOrder points.

In this query, name would return the two-part names (ex PurchaseOrder), and base_object_name would return the three-part names (ex DatabaseA.dbo.PurchaseOrder):

SELECT name, base_object_name FROM sys.synonyms;

If you ever have a database that needs to be repointed from DatabaseA to DatabaseB, you can simply re-point the synonyms via a series of DROP SYNONYM & CREATE SYNONYM statements, without having to perforem ALTER statements for procedures/views/functions/triggers.

Related Question