CODEWITHSUNDEEP
sql-serverdatabasereference
MartinS
MartinS

Reputation: 751

Referencing table in another database

I want to use two SQL Server databases running on one server. I know that there is a possibility to create a SQL query on both databases but from programming scope it's not so much comfortable because I don't have the secondary database yet and code modifying (and also query modifying) is not the best method how to make yourself happy when secondary database will come in (for example from bug reason).

Is there possibility to make something like reference inside primary database (like reference variable in programming language)? So let's say I will have DBP (primary) database and DBS (secondary) databases.

I will create a table in DBS called secondaryTable. And in DBP I create table called secondaryTableReference and tell DBP that this table will be taken from DBS.secondaryTable so every time I will work only with primary DB and it will handle the communication between databases. Is that possible to automate this cross database querying or I have to write every time and everywhere long queries?

I don't like this more DB querying but customer wants that in order of storage and sync reasons.

Thanks.

Upvotes: 0

Views: 555

Answers (1)

Joel Allison
Joel Allison

Reputation: 2111

Yes, the kind of reference you describe is called a table synonym in SQL Server.

USE DBS
GO

CREATE SYNONYM [dbo].[secondaryTableReference] FOR [DBS].[dbo].[secondaryTable]
GO

Then you may query it as though it is a table in your primary database.

SELECT * FROM [dbo].[secondaryTableReference]

Upvotes: 1

Related Questions