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:


Comments (4)
  1. Anonymous says:

    In our case, the three part name is necessary because it accesses an object in another database. Is there any solution that can be applied to this situation?

  2. hshen@mcc.ca says:

    What if my local 3-part names have two different db names?

  3. Barclay Hill says:

    This post describes handling 3 part references to objects i nthe same database.  If you need to reference objects in a different database you add a reference to another project which represents this external database.  See this for more information: msdn.microsoft.com/…/bb386242.aspx

  4. Anonymous says:

    Very helpful! Thanks.

Comments are closed.

Skip to main content