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

  • Anonymous
    September 30, 2010
    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?
  • Anonymous
    November 01, 2011
    What if my local 3-part names have two different db names?
  • Anonymous
    November 01, 2011
    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
  • Anonymous
    April 16, 2014
    Very helpful! Thanks.