Debug School

Cover image for Database references
Suyash Sambhare
Suyash Sambhare

Posted on

Database references

#devops #sql #microsoft #mssql

Database references in SQL projects enable you to incorporate objects that aren't included in a project by linking to another project, .dacpac file, or published NuGet package. The database objects you add to a project can be part of the same database, a different database on the same server, or a different database on a different server. For SQL Server development, use database references to link to another database on the same server for three-part naming, or to link to a different database on a different server for cross-database queries. For databases with a large number of objects in distinct groups, use database references to break up a database into smaller, more manageable projects. Smaller project size can help improve performance and reduce the time required to build a project during iterative local development.

SQL

SQL project file sample and syntax

Include database references in a project through entries in the .sqlproj file, similar to C# projects. Use SQLCMD syntax to reference the database name in SQL project objects. When a database reference points to a different database on the same server, include a <DatabaseSqlCmdVariable> element in the project reference. When a database reference points to a different database on a different server, also include a <ServerSqlCmdVariable> element in the project reference. Database references to the same database don't include <ServerSqlCmdVariable> or <DatabaseSqlCmdVariable> elements.

To include a specific reference to the database reference in the SQL scripts, use SQLCMD variables named in the project file to specify the database name. For example, the following SQL script references a table in the Warehouse database:

SELECT ProductId,
       StorageLocation,
       BinNumber
FROM [$(Warehouse)].[Production].[ProductInventory];
Enter fullscreen mode Exit fullscreen mode 
UPDATE [$(DatabaseName)].[SalesLT].[Customer]
SET [SalesPerson] = 'John Doe',
    [ModifiedDate] = GETDATE()
WHERE [CustomerId] = @CustomerId;
Enter fullscreen mode Exit fullscreen mode

Build with project references

Building a SQL project with database references might require extra configuration to ensure that the referenced objects are available during the build process. For example, if you're building a project in a continuous integration (CI) pipeline, you need to set up the build agent environment similarly to the local development environment.

.dacpac references in the SQL project require that the .dacpac be present on the build agent at the same relative file path as specified in the project file.

Project references in the SQL project require that the referenced project be present on the build agent at the same relative file path as specified in the project file and be able to build successfully on the build agent.
System database references created in original SQL projects in Visual Studio require that the build agent have Visual Studio installed.
NuGet package references in the SQL project require the package be published to a NuGet feed that is also set as a package source for the build agent.

Publish with project references

Publishing a .dacpac built from a project with database references requires no extra steps. The .dacpac file contains the referenced objects and the SQLCMD variables specified in the project file.

For database references to objects in the same database, the objects from the referenced project are included in the .dacpac file but aren't included in the deployment by default. To include the objects in the deployment, use the /p:IncludeCompositeObjects=true option in the SqlPackage command line tool. For example, the following command deploys the AdventureWorks project with the /p:IncludeCompositeObjects=true option to include the objects from database references to AdventureWorks:

Ref: https://learn.microsoft.com/en-gb/sql/tools/sql-database-projects/concepts/database-references

Top comments (0)