TSQL: Create a view that accesses multiple databases

I have a special case,

for example in table ta in database A, it stores all the products I buy

  • SQL query question: SELECT … NOT IN
  • Select a column from a different table in SQL Server
  • distinct binary number in sql server
  • DbGeography make circle with center and radius
  • Rollback the inner transaction of nested transaction
  • Does SQL Server guarantee data integrity?
  • 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?

  • How to get difference between two dates to get result something like Result as 2 Days, 23 hours, 56 mins
  • Is there a tool to build a relationship tree of the stored procedures and functions used in SQL Server 2008?
  • Extremely slow and inefficient query execution from Entity Framework
  • Performance Implications of Comments in SQL Stored Procedures
  • SQL Server/Dynamics CRM: How to read document body from Annotation Base using SQL only
  • SQL: strange query performance behaviour
  • 3 Solutions collect form web for “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.

    Yes, views can reference three part named objects:

    create view A.dbo.viewname as
    select ... from A.dbo.ta as ta
    join B.dbo.tb as tb on ta.id = tb.id
    where ...
    

    There will be problems down the road with cross db queries because of backup/restore consistency, referential integrity problems and possibly mirorring failover, but those problems are inherent in having the data split across dbs.

    As the other answers indicate, you can use the {LINKED_SERVER.}DATABASE.SCHEMA.OBJECT notation.

    You should also be aware that cross-database ownership chaining is disabled by default.

    So within a database, granting SELECT on a view allows a user who may not have SELECT on the underlying tables to still SELECT from the view. This may not work across to another database where the user does not have permissions on the underlying table.

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.