Access data from another database in a stored procedure

Question

Below is my diagram:

• DB_A: schema_1, schema_2, schema_3
• DB_B: schema_3

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

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

Now I can hard code DB_A in my procedures, but when I move the code to the client machine, this will create a problem, since DB_A may not be the same (one of the reasons is that the client is hiding and has QA, Dev and Prod on same car).

The second parameter gets the name DB_A as the parameter, but it will make all the dynamic schema_3 SPS (since I had no way to access something like @ DBName.schema_name.ResourceName).

The third option is to create linked servers that again do not solve my problem for the same reason as the first.

Any idea how to act when I do not want my procedures to be dynamic, because 80% of them are direct.

Change start:

Therefore, I can recount it, since I have several databases with a database having resources (table / view / schema) that need to be partitioned, and then have other databases (one or more) that store the procedures that are calculated according to data from a common database and own database.

, ( ). , , , .

, . SYNONYM , .

Answer 1

, , , .

( DB_B) ( DB_A) . , . . , .

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

Answer 2

DB_A DB_A DB_B:

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

(dev, QA, prod.) : .

Answer 3

DB_A DB_B , , .

Now use [database]. [schema]. [object] when you use other users database

for example: I have two databases ("help desk", "intranet")

from holdesk to 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
;