I am working on some reports that retrieve the area paths for a particular project from the TfsWorkItemTracking database. I want to retrieve those area paths in the same order as which they are displayed in the Team Explorer. (If you're using Team Explorer via VS 2008, and you right-click on a TFS Project, it will give you some options to view the area paths and iterations for that project in a tree view. It also allows you to change the order of those nodes by dragging and dropping.)
Unfortunately, I seem to only be able to find area paths ordered by the Area Path ID's. I've looked at (and queried) several tables/views such as Area, TreesAreUsed, TreeIDPairs, etc (in the TfsWarehouse (sql), and the TfsWorkItemTracking database. Since Microsoft doesn't really provide any official documentation for the backend databases for TFS, this is pretty much a commando mission. So my question is, how does Team Explorer know what order to display the area path tree view in? Am I correct that it must be stored SOMEWHERE in the database? Can anyone identify what table-or-view/fields contains this information?
Oh and as for our environment, we are using TFS 2008/VSTS 2008 with back-end of SQL Server 2008. Please let me know if you need me to provide additional information, as I know that most people aren't trying to do this kind of thing. Also, as a further note, we can't access the TFS API, however, if anyone knows that the order can be retrieved via the API, that might be useful for us to know.
thanks R