Share via

Overview of Cross-Database References

  • Article

You can create or import database objects that refer to objects in other databases. These cross-database references require that you define a corresponding reference in the properties of your database project. If you refer to objects in other databases but you do not define the corresponding references in your project, warnings will appear. For each database that you reference, you can define SETVAR variables that correspond to the server and database that you want to reference. 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 variables are replaced by the values that you specified.

Note

To make cross-database references specific to a particular build configuration, you must define the SETVAR variables for those references in terms of MSBuild variables. For example, you can use one set of MSBuild variables when you build and deploy a database project to a staging server and another set when you build and deploy the same project to a production server. If you define the SETVAR variables for a cross-database reference in terms of server and database names, the reference will function the same way, regardless of which build configuration you specify.

Scenarios for Cross-Database References

Team Edition for Database Professionals 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 is different 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 reference 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 metafiles (.dbmeta) for those other projects and check only the metafiles into version control. By using this approach, you can restrict access to the database projects that contain the schemas that you want to reference. 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 Metafiles

When you add a reference in a database project, you can specify either another database project or a metafile, which is produced when you build a database project.

You should refer to another database project if:

  • You must make changes to the schemas of both the database that will contain the reference and the database to which you are referring. When you deploy the database project that contains the reference, you will also deploy the database project to which you are referring.

You should refer to a database metafile if:

  • 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 database metafile 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: Create Cross-Database References and How to: Rename References to a Server or Database.

The variables and values for the servers and databases that you reference 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 metafile 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: Create Cross-Database References.

  • 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 that resolve to the same value.

Security Considerations

The metafile that is created when you build a database project contains schema information for that project. You should limit access to the metafile to protect that schema information. You can share metafiles with the team by checking them into version control, rather than 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

If you have two database projects, ReportDb and DependentDb, you might want to reference objects in DependentDb from ReportDb. You start by adding a reference in the properties of the DependentDb database project and define variables as follows:

  • RefServer

  • RefDatabase

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 selects all columns from the Employee table in the DependentDb that is deployed to the database that is named DepDb on the server named StageSvr. You would create the object definition for that view as follows:

CREATE VIEW [dbo].[DependentView]
AS
SELECT * FROM [$(RefServer)].[$(RefDatabase)].[dbo].[Employee]
;

See Also

Concepts

Terminology Overview of Team Edition for Database Professionals

Other Resources

How to: Create Cross-Database References
Working with Database Objects
How to: Rename References to a Server or Database