在存储过程中从另一个数据库访问数据

Following is my schema detail:

以下是我的架构细节：

DB_A : schema_1, schema_2, schema_3
DB_A：schema_1，schema_2，schema_3
DB_B : schema_3
DB_B：schema_3

some procedures in schema_3 access resources(table, view, sp) from schema_1 and schema_2.

schema_3中的一些过程从schema_1和schema_2访问资源（table，view，sp）。

All procedures in schema_3 are same on both the dbs. How do I access schema_1 from schema_3 for both the dbs.

schema_3中的所有过程在两个dbs上都是相同的。如何从schema_3访问两个dbs的schema_1。

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).

现在我可以在我的程序中硬编码DB_A但是当我将代码移动到客户端机器时，它会产生问题，因为DB_A可能不相同（其中一个原因是客户端是吝啬的并且在同一台机器上有QA，Dev和Prod）。

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).

第二个选项是将DB_A名称作为参数，但它将使所有schema_3 SP动态化（因为我没有获得任何方法来访问类似@ DBName.schema_name.ResourceName的内容）。

Third option is creating linked servers, which again do not solve my problem because of same reason as first.

第三个选项是创建链接服务器，由于与第一个相同的原因，它再次无法解决我的问题。

Any idea how to proceed, where I do not want my procedures to be dynamic just because 80% of them are straight.

任何想法如何继续，我不希望我的程序是动态的只是因为其中80％是直的。

Edit Start:

编辑开始：

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.

所以我可以重申它，因为我有多个数据库，数据库有资源（表/视图/模式），需要共享，然后有其他数据库（一个或多个），其中有存储过程，计算来自共享数据库和自己的数据数据库。

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.

共享数据库名称在所有环境中都不会保持不变，我想更改它们（特定于环境）。我已经提出了一个解决方案，我将为所有共享资源创建同义词，并且所有过程都将使用它们，这样它们都指的是来自第一个数据库的共享资源。

For each installation I need to modify synonyms definition to reflect correct share database name. Is there any SYNONYM For Database Name, that way I will have way less synonyms to handle.

对于每个安装，我需要修改同义词定义以反映正确的共享数据库名称。对于数据库名称是否有任何SYNONYM，这样我将有更少的同义词来处理。

3 个解决方案

#1

Well the best choice I found is as follows.

那么我找到的最佳选择如下。

Create Synonym (independent database DB_B) for individual objects (in shared database DB_A) with same name in same schema. That way your existing procedures need not change, and will work as required. Synonym gives a good reference on this. I will soon be creating an app to ease creating synonyms for these kind of situations.

为同一模式中具有相同名称的单个对象（在共享数据库DB_A中）创建同义词（独立数据库DB_B）。这样，您现有的程序无需更改，并可按要求运行。同义词为此提供了很好的参考。我将很快创建一个应用程序，以便轻松创建这种情况的同义词。

CREATE SYNONYM DB_B.schema_1.proc_1 FOR DB_A.schema_1.proc_1

#2

You can run your procedure in DB_A and create a view from DB_A to DB_B:

您可以在DB_A中运行过程并创建从DB_A到DB_B的视图：

create view dbo.vw_B_Schema_3
as
select  *
from    DB_B.dbo.Schema_3

You'd have to create three versions of the view (dev, QA, prod.) But the view will be the only difference: procedure definitions can remain identical.

您必须创建三个版本的视图（dev，QA，prod。）但视图将是唯一的区别：过程定义可以保持相同。

#3

If DB_A and DB_B are on same server, only sure you that the login have permission in two database.

如果DB_A和DB_B位于同一服务器上，则只能确保登录在两个数据库中具有权限。

Now, use [database].[schema].[object], when you use object of others database

现在，使用[database]。[schema]。[object]，当你使用别人的对象数据库时

eg: I have two database, ("helpdesk", "intranet")

例如：我有两个数据库，（“帮助台”，“内联网”）

from heldesk to intranet

从heldesk到Intranet

create view dbo.users
as 
select login, name, lastname
from intranet.dbo.user // [database].[schema].[object] user is a table in dbo schema from intranet database.
where status = 1
;

#1