This Q&A was previously posted on the Stack Exchange Network of websites.
This content is licensed under the Creative Commmons Attribution-Sharealike (CC BY-SA) license. As the author of these answers, this archive serves as a canonical location for permalinks, which will outlive Stack Overflow. See all my answers by looking at the Stack Exchange post category.
SQL Visual Studio Find and Replace in Publish Profile
Question
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
asked 2018-10-29 by user162241
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.
answered 2018-10-30 by Andy Mallon