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

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]asselect a.ID, a.SomeInfo, b.SomeOtherInfofrom TableInA ajoin DatabaseB.dbo.TableInB bon -- 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.


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.


Yes, views can reference three part named objects:

create view A.dbo.viewname asselect ... from A.dbo.ta as tajoin B.dbo.tb as tb on ta.id = tb.idwhere ...

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.