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:

CREATE VIEW vWhatever

AS

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)

GO

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

UNION ALL

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