如何在DbA中创建引用DbB中的表的视图？(How can I create a view in DbA that references a table in DbB?)

如何在DbA中创建引用DbB中的表的视图？(How can I create a view in DbA that references a table in DbB?)

是否可以在数据库A中设置引用数据库B中的表的视图？

我收到以下错误：

无法模式绑定视图'dbo.AGView'，因为名称'dbB..AG2Table'对于模式绑定无效。 名称必须采用两部分格式，并且对象不能引用自身。

USE [dbA] GO IF EXISTS(SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[AGView]')) DROP VIEW [dbo].[AGView] GO USE [dbA] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE VIEW [dbo].[AGView] WITH SCHEMABINDING AS SELECT ag.Id AS [AGId], ag.Name AS [AGName] FROM dbB..AG2Table agcag JOIN dbB..AGTable ag on ag.Id = agcag.Id GO

Is it possible to setup a view in Database A that references a table in Database B?

I'm getting the following error:

Cannot schema bind view 'dbo.AGView' because name 'dbB..AG2Table' is invalid for schema binding. Names must be in two-part format and an object cannot reference itself.

USE [dbA] GO IF EXISTS(SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[AGView]')) DROP VIEW [dbo].[AGView] GO USE [dbA] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE VIEW [dbo].[AGView] WITH SCHEMABINDING AS SELECT ag.Id AS [AGId], ag.Name AS [AGName] FROM dbB..AG2Table agcag JOIN dbB..AGTable ag on ag.Id = agcag.Id GO

最满意答案

您可以使用3部分命名[databaseName].[schemaName].[tableName]创建一个使用链接服务器到另一个数据库的视图[databaseName].[schemaName].[tableName]

您不能拥有WITH SCHEMABINDING子句。 使用WITH SCHEMABINDING可防止对视图中使用的表进行架构修改。 AGView中的视图AGView无法确保表格形式的dbB尚未被修改。

You can create a view that uses a linked server to another database using 3-part naming [databaseName].[schemaName].[tableName]

You cannot have the WITH SCHEMABINDING clause. Using WITH SCHEMABINDING prevents schema modifications on the tables used in the view. The view AGView within dbA has no way to ensure the schema of the table form dbB has not been modified.