SQL Server: Joining Tables from Different Databases on the Same Server

Today I came across a requirement where I had to write a SQL query joining tables from different databases on the same SQL Server. I thought of sharing the simple concept with you.

For querying multiple tables in different databases on the same server, all we have to do is use the fully qualified table name. The only condition is, the user logged into the query analyzer (or used for executing the query) should have permission on both the databases.

For example, I have two databases MtbDatabaseOne and MtbDatabaseTwo in the SQL Server. MtbDatbaseOne has a table named TableOne. MtbDatabaseTwo has a table named TableTwo.

Joining these two tables in a select query is very simple. The select query will be similar to the one we normally use to select data from multiple tables in the same database. The only difference is instead of just mentioning the table name, we have to specify the fully qualified table name. The fully qualified table name looks like [Database Name].[database owner name].[table name].

In my example the fully qualified table name for TableOne is [MtbDatabaseOne].[dbo].[TableOne] and TableTwo is [MtbDatabaseTwo].[dbo].[TableTwo]

So the select query looks like this:

select t1.ID, t1.Name, t2.Name
from [MtbDatabaseOne].[dbo].[TableOne] as t1
inner join [MtbDatabaseTwo].[dbo].[TableTwo] as t2 on t1.ID = t2.ID

SQL Server Query To Joining tables from different databases

SQL Server Query To Join-Tables From Different Databases

The SQL queries specified in this article is tested on SQL Server 2014.

Related Articles:

Related Articles & Tools

2 comments for “SQL Server: Joining Tables from Different Databases on the Same Server

  1. Veeresh
    Jan 10, 2017 at 1:52 pm

    Getting table name invalid

    • Jan 10, 2017 at 9:43 pm

      Hi Veeresh,
      Please see if the logged in user has at least select permission for both the databases.

Leave your thoughts...