Using local 3-part names in programmability objects

VSTS:DB does not support self-referencing 3 part names. It does support external 3 and 4 part references. For folks wondering what the difference is it is basically the use of fully qualified 3 part names identifiers for all objects in your programmability objects including local objects. For instance you would refer to a table that is in the current database from a stored procedure using the 3-part name like so: [Local Database Name].[Schema Name].[Object Name].

While VSTS:DB does not support local 3 part names it does support the use of variables and literals to resolve references to external databases. The $(DatabaseName) variable is an ambient variable that will have its value replaced at the time of deployment. This variable gets its value from the project properties deployment tab. Since $(DatabaseName) is always replaced at deployment with the target database name and references through variables are resolved you may use a variable in your local 3-part names.

Our guidance is to not use local 3-part names as it introduces an unnecessary layer of abstraction and dependency on the database name. For teams that use local 3 part names and would like to continue using local 3-part names can use the $(DatabaseName) variable to resolve local references. This would make your object references look like this: [ $(Database Name) ].[Schema Name].[Object Name].

To update all the local 3-part names to use a variable can be done through refactoring. To refactor all of your 3 part names to use the variable you do the following:

1. Right click on your project and select Refactor -> Rename Server/Database References

2. On the window, uncheck the box for “Replace the name of a server or server variable”

3. Check the box for "Replace the name of Database or a database variable”

4. Enter in the name of your database in the Old name field: ex “ThreePart”

5. Enter $(DatabaseName) for the New Name

6. Click OK

7. Click Apply on the preview window

Refactoring will update all the local 3 part names to use the variable $(DatabaseName) for the Database segment of the 3 part name reference. Your references to local 3 part named objects should now all be resolved. Below are the screen shots showing the UI in steps 1-7.

Thanks to Tom, a QA Lead on the team, for identifying this work around.

Step 1:

 

Step 2-6:

 

Step 7: