Search code examples
sql-serverdatabaseviewschema

How to create schema bound, cross-database view in SQL Server

I am attempting to create an indexed view on SQL Server 2008. I have a master database in which I cannot make any changes to (in terms of adding tables, views, etc.). However, I need to create some different views for various reasons that need to work with live data.

I have created a new database along side my master database so I can create views there. I am able to create views just fine, but I want to index some of the larger views. However, when I try to create a schema bound view cross-database, I receive the following error:

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

Since I am going cross-database with the views, I have to reference the name in three-part format.

My creation statement for the view:

CREATE VIEW dbo.Divisions WITH SCHEMABINDING AS
SELECT master.dbo.hbs_fsdv.seq_              AS DivisionID, 
       master.dbo.hbs_fsdv.fs_division_desc_ AS Description 
  FROM master.dbo.hbs_fsdv

How can I create an indexed cross-database view in SQL Server?

Solution

  • Plain and simple. You can't. From the MSDN page:

    The view must reference only base tables that are in the same database as the view.

    https://msdn.microsoft.com/en-us/library/ms191432.aspx