If you create or import database objects that refer to objects in other databases, you must define a corresponding reference in the properties of your database project. For each database to which you refer, you can define SETVAR variables that correspond to the server and database to which you want to refer. When you modify an object definition to include the reference, you can define it by using these variables instead of the explicit names of the server and the database. When you build the database project, the values that you specified will replace the variables.
Scenarios for Cross-Database References
Visual Studio Team System Development Edition supports the following scenarios:
You created a database project and imported the schema from an existing database. The schema contains objects that refer to objects in one or more other databases. In the properties of the project, you define a corresponding reference and replace the explicit names of the server and the database with SETVAR variables. Because of this definition, the cross-database references can be validated at design time. You can also deploy the database project to an isolated development environment, where the name of the target server might differ from the name of the target server in your production environment.
You have a database project that contains cross-database references, and those references use SETVAR variables to identify servers and databases. You want to change the names of those variables and update the object definitions that contain those references.
You want to refer to additional database schemas from the database project that you are developing. You want to share only those schemas with the team without having to share the other database projects with them. You can add a reference to the database schema files (.dbschema) for those other projects and check only the schema files in to version control. By using this approach, you can restrict access to the database projects that contain the schemas to which you want to refer. You should also use this approach if you want to deploy a database project but not any projects that depend on it.
You can use database refactoring to perform additional tasks with cross-database references. For more information, see How to: Rename References to a Server or Database.
References to Database Projects and Schema Files
When you add a reference in a database project, you can specify either another database project or a schema file, which is produced when you build a database project.
You should refer to another database project if the following statement is true:
You should refer to a schema file if any one of the following statements is true:
You must refer to a database whose project is not in the current solution.
You must deploy a single project without deploying the projects upon which it depends.
You are referring to a database schema that is not likely to change. In this scenario, you can check the schema file in to version control.
Limitations of Cross-Database References
When you import a database schema, references to other databases in the object definitions produce warnings because the database project cannot validate those references before you deploy it. After you define database references in the properties of the project, you can validate the object definitions and resolve the warnings. You can then update the object definitions by replacing the names of servers and databases with variables. You can also use refactoring to perform this substitution. For more information, see How to: Add References to Database Projects and How to: Rename References to a Server or Database.
The variables and values for the servers and databases to which you refer are not specific to a build configuration. To specify the server and database at a command prompt, you must refer to them in terms of MSBuild variables, which can be substituted at deployment time.
The following limitations also apply:
You can add a reference to a database only if it has a corresponding database project.
You must build a database project before you can add a reference to the schema file for that database project.
You must build the database projects to which you are referring so that cross-database references to them will resolve without warnings.
If you change the build configuration of the solution or of the database projects to which you are referring, you might need to rebuild the solution so that cross-database references to them will resolve without warnings.
If you refer to an object in a database project on another server, you must define it as a linked server on your target server. For more information, see How to: Add References to Database Projects.
You do not need a cross-database reference to refer to the master database.
References that include SETVAR variables must always take the form "[$(VariableName)]". If you omit the brackets, you might not be able to deploy the database project.
Names of SETVAR variables must be unique. If your server has the same name as your database, you must define variables that have different names but resolve to the same value.
Security Considerations
The schema file that is created when you build a database project contains schema information for that project. You should limit access to this type of file to protect that schema information. You can share schema files with the team by checking them in to version control, instead of by deploying them to the target server. If the database project for the database to which you are referring is in the same solution, other developers on the project will have access when they synchronize to version control and build the solution.
Example of a Cross-Database Reference
If you have two database projects, ReportDb and DependentDb, you might want to refer to objects in DependentDb from ReportDb. You start by adding a reference in the properties of the DependentDb database project and defining variables as follows:
The value for RefServer is the name of the server upon which DependentDb resides. The value for RefDatabase is the name of the target database that is deployed by the DependentDb project. In this example, RefServer has the value StageSvr, and RefDatabase has the value DepDb.
You could add a view to ReportDb that shows all columns from the Employee table in the DependentDb. You deploy the table to the database that is named DepDb on the server that is named StageSvr. You would create the object definition for that view by adding the following statements:
CREATE VIEW [dbo].[DependentView]
AS
SELECT * FROM [$(RefServer)].[$(RefDatabase)].[dbo].[Employee]
;