TSQL: Create a view that accesses multiple databases

database, sql-server, t-sql, view

Solution

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.

Problem

I have a special case, for example in table `ta` in database `A`, it stores all the products I buy ``` table ta( id, name, price ) ``` in table `tb` in database `B`, it contain all the product that people can buy ``` table tb( id, name, price .... ) ``` Can I create a view in database `A` to list all the products that I haven`t bought?

Original source