Geek Answers Handbook

Direct TFS Warehouse query using T-sql

Hey. I am wondering if anyone has experience maintaining a Team Foundation Server repository to retrieve current work item information. I understand that the required information is stored in the TFSWarehouse database.

I know well that there are APIs that provide this functionality, and I used them myself. However, this is due to the requirement to install Team Explorer on any client PC that will refer to the API.

All I want is a list of work items with some fields selected, such as Title, State and Created By.

Has anyone tried to do this themselves, and does anyone really know any pitfalls with this approach?

---------- EDIT 16/02

Just add after talking to TreeUK. Would it be nice if someone could have a sample query or maybe some explanation regarding the structure of the table?

+4
source share
4 answers

If you have access to the SQL database hosting TFS, you can write a query that retrieves this information.

The database you want to look at is TFSWarehouse. The following is a query that contains general work item information.

select System_Id as WorkItemId, System_Title as Title, System_State as Status, System_Reason as Reason, System_WorkItemType as Type from [Work Item]

As it is clear that they all do, check this table to get any other properties of interest.

If you need anything else, let me know. Otherwise, mark this as an answer :)

+4
source

TFS 2005, this should be [workitemtracking] .dbo. [workitemsare]

+1
source 
 SELECT DISTINCT Top(100) FactWorkItemLinkHistory.WorkItemLinkTypeSK, DimWorkItemLinkType.LinkName, DimWorkItem.System_Id, DimWorkItem.System_Title FROM FactWorkItemLinkHistory INNER JOIN DimWorkItemLinkType ON DimWorkItemLinkType.LinkID = FactWorkItemLinkHistory.WorkItemLinkTypeSK INNER JOIN DimWorkItem ON DimWorkItem.System_Id = FactWorkItemLinkHistory.SourceWorkItemID WHERE FactWorkItemLinkHistory.WorkItemLinkTypeSK = 2
0
source

TFS 2013, it will be [Your Tfs_Warehouse Database] .dbo.DimWorkItem

 SELECT System_Id as WorkItemId, System_Title as Title, System_State as Status, System_Reason as Reason, System_WorkItemType as Type FROM dbo.DimWorkItem
0
source

More articles:


All Articles