Managing Database References in Scripts (Stored Procs)

Martin AatmaaMartin Aatmaa Posts: 2
edited March 24, 2010 4:27PM in SQL Compare Previous Versions
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!

Comments

  • Thanks for your post.

    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.
    Chris
Sign In or Register to comment.