How to: Create Cross-Database References

  • Article

To refer to an object in another database from a database project, you must take the following steps:

  • Make sure that the database to which you want to refer has an associated database project. For more information, see How to: Create Database Projects.

  • In your database project, add a reference for the server and the database to which you want to refer and the server on which it will be deployed, and define SETVAR variables for that database and its server. If the database that will contain the reference is on the same server as the database to which you want to refer, then you do not need to specify a variable for the server.

  • In your object definition, include a cross-database reference that uses the SETVAR variables that you just defined.

Note

If you create or import a schema that contains a cross-database reference, you must update it to refer to the server and the database by SETVAR variables, instead of by name. Otherwise, a warning will appear in the Error List window for each of these references. This warning appears because the object to which you are referring might not exist when you deploy your project. After you replace the names of the server and the database with SETVAR variables, you can use database refactoring to update the names and values of those variables.

To add a reference in a database project

  1. Open the solution that contains the database project to which you want to add a reference.

    For more information, see How to: Open a Database Project.

  2. On the Project menu, click Add Database Reference.

    The Add Database Reference dialog box appears.

  3. To add a reference for a database project:

    1. Click Database projects in the current solution.

    2. In the list, click the database project for which you want to add a reference.

  4. To add a reference for a metafile:

    1. Click Database project metafile (.dbmeta).

    2. In the box, type the path and file name of the metafile, or click Browse to specify the metafile for which you want to add a reference.

  5. To define a SETVAR variable for a server:

    1. Click Define server variable.

    2. In Name, type a name for the variable that represents the server.

    3. In Value, type the name of the server that the variable represents.

  6. To specify a SETVAR variable for the database to which you want to refer:

    1. Click Define database variable.

    2. In Name, type the name for the variable that represents the database.

    3. In Value, type the name of the database that the variable represents.

  7. Select the Update the existing schema object definitions and scripts to use the database reference variables check box if you want to update the schema objects and scripts in the database project, replacing the server and database names with the variables that you just defined.

  8. Click OK.

  9. If you selected the Update the existing schema object definitions and scripts to use the database reference variables check box, the Preview Change - Rename Server or Database dialog box appears.

    1. (Optional) You can clear the check box next to one or more of the changes in the list if you do not want that change to be applied.

    2. (Optional) You can click any change to display the details of the change in the Preview changes pane.

    3. Click Apply to update the schema objects and scripts that you specified.

      Those objects and scripts are modified to use the SETVAR variables instead of the names of servers and databases that you specified.

    The reference is added to the database project.

To update the values of SETVAR variables

  1. In Solution Explorer, click the database project that contains the SETVAR variables that you want to update.

  2. On the Project menu, click ProjectName Properties.

    The Properties window appears.

  3. Click the References tab.

    A list appears that displays all the referenced databases, their server and database variables, and the values of those variables.

  4. Click Edit Variables.

    The Reference Variables dialog box appears.

  5. To update the value of a SETVAR variable for a server, in Server Variable Value, type the name of the server to which you want to refer when you deploy the database project.

    Note

    Although you can update the names of the variables by using this method, it does not update occurrences of those variables in scripts or definitions of schema objects. To rename references to a server or database, see How to: Rename References to a Server or Database.

  6. To update the value of a SETVAR variable for a database, in Database Variable Value, type the name of the database to which you want to refer when you deploy the database project.

  7. Click OK.

  8. On the File menu, click Save All.

To include a cross-database reference in an object definition

  1. Open an object definition in the Transact-SQL (T-SQL) editor. For more information, see How to: Create a Database Object or How to: Modify Database Objects.

  2. In the object definition, include a cross-database reference of the form [$(ServerVariableName)].[$(DatabaseVariableName].[Schema].[ObjectName].

    When you build and deploy the database project, the values of the SETVAR variables replace the names of those variables.

  3. On the File menu, click Save All.

See Also

Tasks

How to: Configure Database Projects for Build and Deployment

Concepts

An Overview of Database Build and Deployment
Terminology Overview of Team Edition for Database Professionals

Other Resources

Overview of Cross-Database References
How to: Rename References to a Server or Database