Query Multiple Databases

"Query Multiple Databases"
This search term has been used 13 times in the last week on this blog. So here is a little blog post about it
It's really not complicated to query multiple databases. You have to have permissions on both databases
The syntax looks like this:
SELECT *
FROM Database1.ObjectOwner.TableName
JOIN Database2.ObjectOwner.TableName ON.....

So here is a little script so that you can test it out

USE master
GO

--Create the first Database
CREATE DATABASE DBtest1
GO

--Create the second Database
CREATE DATABASE DBtest2
GO



USE DBtest1
GO

--Create Table1 on DBtest1
CREATE TABLE Table1(id INT, DescriptionValue VARCHAR(49))
INSERT Table1 VALUES (1,'DBtest1')
GO


USE DBtest2
GO

--Create Table2 on DBtest2
CREATE TABLE Table2(id INT, DescriptionValue VARCHAR(49))
INSERT Table2 VALUES (1,'DBtest2')
GO



USE master
GO

--Do the join between DBtest1 and DBtest2
SELECT *
FROM DBtest1.dbo.Table1 t1
JOIN DBtest2.dbo.Table2 t2 ON t1.id = t2.id
GO


--Another way
SELECT *
FROM DBtest1..Table1 t1
JOIN DBtest2..Table2 t2 ON t1.id = t2.id
GO



--Drop these databases
DROP DATABASE DBtest1,DBtest2