sql server - TSQL: Create a view that accesses multiple databases

Question

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?

Answer 1

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.