Managing Database References in Scripts (Stored Procs)
Martin Aatmaa
Posts: 2
Is there a good way to manage database references in scripts with SQL Compare?
For example, let's say I have a system with two databases:
DBX
DBY
A number of scripts in DBX rely on DBY. In such cases, those scripts in DBX refer to DBY using the three part notation:
DBY.dbo.StoredProcName
The question is, how do I manage these references in the Red Gate workflow?
For example, if I need to deploy the above system to production, where the databases are named differently:
DBX_prod
DBY_prod
How do I ensure that the references in the DBX scripts to DBY have the correct (desired) database name?
In our Visual Studio Database projects, this was solved with SQLCMD variables.
On project initialization, we would define a "database reference" variable, which would then replace the DB part of all three part DB references.
On deploy, the variable could be set to any value, and the deploy script would automagically replace the DB reference variables with the desired value.
Is there something similar that can be done in SQL Compare?
Thank you in advance for any input!
For example, let's say I have a system with two databases:
DBX
DBY
A number of scripts in DBX rely on DBY. In such cases, those scripts in DBX refer to DBY using the three part notation:
DBY.dbo.StoredProcName
The question is, how do I manage these references in the Red Gate workflow?
For example, if I need to deploy the above system to production, where the databases are named differently:
DBX_prod
DBY_prod
How do I ensure that the references in the DBX scripts to DBY have the correct (desired) database name?
In our Visual Studio Database projects, this was solved with SQLCMD variables.
On project initialization, we would define a "database reference" variable, which would then replace the DB part of all three part DB references.
On deploy, the variable could be set to any value, and the deploy script would automagically replace the DB reference variables with the desired value.
Is there something similar that can be done in SQL Compare?
Thank you in advance for any input!
Comments
This scenario isn't particularly easy to handle using SQL Compare.
Generally, objects will only have three part name if they are used cross database, so normally this would be a meaningful difference that you would want to synchronize.
There are of course situations where this is not the desired behaviour, but we haven't implemented anything within the tool to help. We have an open feature request (SC-3585) to allow the user to ignore the database name, but this would mean that the database name wouldn't be in the script at all.
I'm assuming that you need the database name; you just need it reassigned to target db name, rather than keeping the source db name.
I think the only workaround would be to output the sync script, and then perform a find and replace.
I'm sorry I can't be of more help.