Query Across Databases

Transact-SQL
1

Hi, I have two databases on the same server.

I have been able to query like the following:

SP is in DatabaseA

USE DatabaseA
SELECT * FROM DatabaseB.dbo.Table1

Obviously, a LinkServer has already been setup.

Question: is there a better way to refer to DatabaseB?

2

on same server there is no need of linked server. as the name implies linked server is best suited when you are linking 2 different servers

your query is fine and can work without a linked server as long as your other database is actually named: DatabaseB

1 Like
3

Question: is there a better way to refer to DatabaseB?

MS, and others, strongly recommend that you don't include a db name in T-SQL. Instead, they suggest using synonyms or views (when a synonym will not be good enough).

For example:
USE DatabaseA;
CREATE SYNONYM dbo.Table1 FOR DatabaseB.dbo.Table1;
--then:
SELECT * FROM dbo.Table1 --will read from DatabaseB because of the synonym
1 Like
4

I vehemently agree with what Scott posted and not just because of some MS recommendation. If you only do 2 part naming everywhere except in synonyms or pass through views, then your code becomes a whole lot more portable and easily allows for such things as multiple differently named databases that are actually from the same restore without ever having to go into code and change the names. It also makes it so that you don't have to change code when you go from Dev to Test to Staging to prod, etc, etc.

2 Likes
5

Thank you! I just learned something new (CREATE SYNONYM)!

6

yosiasz, you are correct. I was having trouble to do call a function from DatabaseB when I was on DatabaseA.

But when I switched over to "Use DatabaseB" and still referred to the tables from DatabaseA, it worked.

And yes, without the linkserver, it still worked. Thanks

7

That's actually a mistake to call the function that way. You've just made your code dependent on a different database by hardcoding the name into a USE statement. Consider making a copy of the function in the database that doesn't have it.

8

Hi Jeff,

I agree with you but unfortunately, the database that doesn't have the function, it is a read-only database.