title: Same dbs with different names on same server. Need a way to manage the db objects
body: <p><strong>Background</strong></p>
  
  <p>We are UK based and have a couple of databases: DbFront_UK and DbBack_UK. We have recently ventured into the US and now have 2 new Dbs: DbFront_US and DbBack_US. DbFront contains sprocs that perform cross db joins to DbBack and vice-versa. A US and UK database should never communicate.</p>
  
  <p>The US code and Db changes were done very quickly and separate to the UK changes. Now I have a task to merge the codebase and dbs. Merging the code has been easy however, the dbs are way harder. There are many sprocs that join as <strong>DbFront_UK.schema.table</strong> so I can no longer have a single SP that I can deploy to both Dbs. </p>
  
  <p><strong>Restrictions:</strong></p>
  
  <p>All 4 of these databases are on the same server in Rackspace. There is no way to split them and I can have only 1 SQL instance. </p>
  
  <p><strong>Question:</strong></p>
  
  <p>What's the best way to deliver a single set of database objects that can be deployed and managed easily? We are continually enhancing our product so the likelihood of SP changes are high. </p>
  
  <p><strong>Options:</strong></p>
  
  <ol>
  <li>I can find all SPs and objects that reference the other Db and add a suffix to the SP, one for UK and one for US (so <strong>sp_one</strong> becomes doubled as <strong>sp_one_UK</strong> and <strong>sp_one_US</strong>). Then in my C# code, I can do <code>if UK call this SP, if US call this one</code>. The benefit here is that it's probably easier to identify what SP goes where and it will be easier for new developers to understand. The danger is that it is a lot of tedious work and is ugly, there would be SPs existing in the US dbs with a UK suffix that never get called. But we have the same set of Db objects for both environments</li>
  <li>Leave them as-is. Sp1 on UK will still be named SP1 on US. The big danger here is that we end up duplicating changes and they will very easily get missed. There are a lot of developers here and these changes will likely get missed, future maintainability is an important factor</li>
  <li>I can run sql in sqlcmd mode and have the Db name as a variable. I think this would effect the execution plan and could lead to poor SQL performance, thoughts around this would be appreciated. </li>
  </ol>
  
  <p>Any thoughts are appreciated.</p>
  
  <p>Thanks</p>