Using References in Database Projects

  • Article

You can add several types of references in database projects to handle different situations. The procedures that you use vary slightly for different types of references.

The following table shows the types of references that you can add and the situations that each type addresses.

Reference type

Reason for use

A database project (.dbproj) in the current solution

You would add this type of reference to enable several scenarios.

  • You can refer to a database object in one project from another project by adding the reference and defining a variable for the name of the server (optional) and a variable for the name of the database that contains the object. You use these cross-database references if your database refers to objects in other databases. For more information, see Starting Team Development of Databases that Reference Other Databases.

If you add a reference to another project but define no variables, you create a composite project. When you deploy this type of project, you also deploy the objects in other projects. For more information, see Composing Projects by Using References later in this topic.

The output of a database project (.dbschema file), from either the current solution or a different solution

If you must refer to objects in another database but you want to isolate that database from other developers, you would add a reference to a database schema file. You might take this approach either because you want to prevent changes or because you cannot update the database. For example, you might add a reference to a .dbschema file if your database project must refer to logins or keys that are shared and defined on the server but used by multiple database projects. For more information, see Starting Team Development of Databases that Reference Shared Server Objects.

You might want to reference a .dbschema file that contains the definitions for your server objects, and you might not want to verify the settings on the server when you deploy your database project. For this scenario, you could add a reference to the master.dbschema file and define a literal for the database variable whose value is "master". By doing this, your references to objects defined in the "master" database can be resolved without errors. For more information about literals, see Referencing Databases by Using Literals later in this topic.

A master.dbschema file that contains definitions of system objects

If you want to reference system objects such as system stored procedures, system tables, system views, or system catalogs, you must add a reference to the appropriate master.dbschema to resolve those references. For example, you might want to reference sys.sysobjects in a stored procedure. A master.dbschema file is provided with Visual Studio Premium for each supported version of SQL Server. You can find the .dbschema files in [Program Files]\Microsoft Visual Studio 10.0\VSTSDB\Extensions\SqlServer\Version\DBSchemas, where Version is the version of SQL Server that you are using (such as 2005 or 2008).

For this scenario, you add a reference to the appropriate master.dbschema file and define a literal for the database variable whose value is "master". By doing this, your references to objects defined in the "master" database can be resolved without errors. For more information about literals, see Referencing Databases by Using Literals later in this topic.

A SQL common language runtime (CLR) project in the current solution

You would reference a SQLCLR project in the current solution if you are developing one or more SQLCLR objects that you want to deploy with your database project. When you deploy the database project, you also deploy the assembly that is produced by the SQLCLR project to which you refer. The CREATE ASSEMBLY statement is built from properties that you specify for that reference. For more information, see Starting Team Development of Databases that Reference SQLCLR Objects and Using Cross-Database References later in this topic.

A SQLCLR assembly, either from a project in the current solution or from a different source

You would add a reference to a SQLCLR assembly if your database project must refer to objects that are defined in that assembly but that are not being developed at the same time or by the same team. When you deploy the database project, you also deploy the assembly to which you refer.

An XML schema definition, as defined in an .xsd file

You must register a schema before you can associate it with a variable, a parameter, or a column to create typed XML variables, parameters, and columns. You might want to use typed XML if any one of the following statements is true:

  • You have schemas for your XML data and you want the server to validate that data according to the schemas.

  • You want to optimize storage and queries based on type information.

  • You want to take better advantage of type information when your queries are being compiled.

For more information, see Starting Team Development of Databases that Use XML Schema Collections.

Defining Cross-Database References

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 Premium 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 must change 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 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:

  • 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 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]
;

Creating Composite Projects by Using References

Database and server projects cannot address all scenarios that you might encounter during database development. To address scenarios that are more complex, you can create composite projects from two or more other projects.

You create a composite project when you add a reference in a database project to another database or server project but do not provide values for the server and database variables in the projects to which you are referring. When you add this type of reference, the project to which you added the reference includes all objects and settings in the project to which the reference points.

By creating a composite project, you enable the following scenarios:

  • You can manage database schemas that refer to shared logins or keys. For more information, see Starting Team Development of Databases that Reference Shared Server Objects.

  • You can break a large database into subprojects, either to increase performance or to separate schemas or object types. You might also want to make this change if multiple databases share some objects but differ in other ways. You can break a project into two or more subprojects only if that change will not create a circular reference.

When you deploy the composite project, you also deploy the objects in all the database projects to which it refers. For more information, see Starting Team Development of Large Databases.

Referencing a Database by Using Literals

You can define database reference variables to specify the name of a referenced database in your target deployment environment. If you do not want to use SQLCMD variables, because the target database name is always the same in your deployment environment, you can instead specify a literal whose value is the name of the referenced database.

If you use variables, your syntax might appear as follows:

CREATE VIEW [dbo].[View1] AS SELECT * FROM [$(MasterDbVar)].[dbo].[spt_values].

If you instead use a literal, your syntax might appear as follows:

CREATE VIEW [dbo].[View1] AS SELECT * FROM [master].[dbo].[spt_values]

For more information about how to specify a literal value, see How to: Add References to Database Projects.

See Also

Tasks

How to: Rename References to a Server or Database

How to: Add References to Database Projects

Concepts

Creating and Modify Database and Server Objects