title: Accessing data from another database in stored procedure
body: <p>Following is my schema detail:</p>
  
  <ul>
  <li>DB_A : schema_1, schema_2, schema_3 </li>
  <li>DB_B : schema_3</li>
  </ul>
  
  <p>some procedures in schema_3 access resources(table, view, sp) from schema_1 and schema_2.</p>
  
  <p>All procedures in schema_3 are same on both the dbs. <strong>How do I access schema_1 from schema_3 for both the dbs</strong>.</p>
  
  <p>Now I can hard code DB_A in my procedures but when I move code to client machine, it will create a problem since DB_A may not be same(one of the reason being client is miser and having QA, Dev and Prod on same machine).</p>
  
  <p>Second option is getting DB_A name as a parameter, but it will make all the schema_3 SPs dynamic (as I did not get any method to access something like @DBName.schema_name.ResourceName).</p>
  
  <p>Third option is creating linked servers, which again do not solve my problem because of same reason as first.</p>
  
  <p>Any idea how to proceed, where I do not want my procedures to be dynamic just because 80% of them are straight.</p>
  
  <p>Edit Start:</p>
  
  <p>So I can restate it as I have multiple databases with a database having resources (table/view/schema) which needs to be shared and then having other databases (one or more) which have stored procedures which computes on data from shared database and self database.</p>
  
  <p>Shared database name is not going to be constant on all the environments and I want to change them(environment specific). I have come out with a solution where I will be creating synonym for all the shared resources and all procedures will be using them, that way they are all referring to shared resources from first database.</p>
  
  <p>For each installation I need to modify synonyms definition to reflect correct share database name. Is there any <a href="http://technet.microsoft.com/en-us/library/ms177544.aspx" rel="nofollow">SYNONYM</a> For Database Name, that way I will have way less synonyms to handle.</p>