Query multiple databases

Queries from multiple databases on the same server
March 19, 2017 – 03:05 pm
Across DB - sql query accross multiple databases - across db

If the serial is on the main table from EACH database, then you can just join the tables together using the fully qualified name:



SELECT (whatever you want to select)

FROM [Database A].[schema - usually dbo].DSNA_tblMaiin AS a

JOIN [Database B].[dbo].DSNB_tblMaiin AS b ON a.Serialnumber = b.SerialNumber

JOIN [Database C].[dbo].DSNC_tblMaiin AS c ON a.SerialNumber = c.SerialNumber

WHERE (whatever you want to filter by)


If the assets were in one of the tables, but not all three (and you didn't know which one), you could use a UNION within the view to accomplish it - it will essentially bring all three tables together, appending the fields that you specify (of the same datatype) until you have one large "virtual table" ex:

SELECT col1, col2, SerialNumber, etc.

FROM [Database A].[dbo].DSNA_tblMaiin


FROM [Database B].[dbo].DSNB_tblMaiin

FROM [Database C].[dbo].DSNC_tblMaiin

Dot notation is exactly what you're looking at. And if you were extending the query to another server, it would just be [ServerName].[DatabaseName].[ObjectSchema].[ObjectName] instead of [DatabaseName].[ObjectSchema].[ObjectName]

Source: social.msdn.microsoft.com
Related Posts