TSQL: Create a view that accesses multiple databases
Solution 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.
Solution 2
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.
Solution 3
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.
Prophet
Updated on July 09, 2022Comments
-
Prophet almost 2 years
I have a special case,
for example in table
ta
in databaseA
, it stores all the products I buytable ta( id, name, price )
in table
tb
in databaseB
, it contain all the product that people can buytable tb( id, name, price .... )
Can I create a view in database
A
to list all the products that I haven`t bought? -
Prophet over 14 yearslet me try.. yes..all my database are in the same server.
-
Vivian River over 11 yearsI would like to add that I did something like this. However, it should be noted that if you want to specify the name of the database in which to create the view, you cannot use
CREATE VIEW abc.dbo.YourView
. You must instead runUSE abc
and thenCREATE VIEW dbo.YourView
Otherwise, you will get an error. -
Jecoms over 7 yearsYep. The SQL user accessing the view needs to have read permissions for any referenced database.
-
Dodecaphone about 2 yearsWhat I notice is that while this works absolutely fine in SQL Server Management Studio, Visual Studio pitches a fit whenever I fully qualify a reference within a view (even to the SAME database). I haven't got to the bottom of that yet.