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: