TSQL: Create a view that accesses multiple databases

Yes you can – the t-sql syntax is the same as within any other cross database call (within a stored procedure for example).

To reference your tables in the second database you simply need:

[DatabaseName].[Schema].[TableName]

So you would end up with something like

CREATE VIEW [dbo].[YourView]
as
select 
a.ID, 
a.SomeInfo, 
b.SomeOtherInfo
from TableInA a
join DatabaseB.dbo.TableInB b
on -- your join logic goes here

Note that this will only work on the same server – if your databases are on different servers them you will need to create a linked server.

Leave a Comment

tech