Directly query TFS Warehouse using T-sql

Question

Hi I'm wondering if anybody has had any experience querying a Team Foundation Server store to retrieve current work item information. It is my understanding that the information I require is stored in the TFSWarehouse Database.

I'm well aware that there are APIs that provide this functionality and I've used them myself. However these come with the requirement to install Team Explorer on any client PC that will reference the API.

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

Has anybody attempted this themselves, and indeed does anybody know of any pitfalls with this approach?

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

Just to add after speaking with TreeUK. What would be great is if anybody perhaps has an example query, or maybe some explanation as to the table structure?

Answer 1

If you have access to the SQL database that hosts TFS, you can write a query that pulls this information out.

The database you want to look at is TFSWarehouse. Below is a query that gets Work Item general 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]

It's kind of clear what they all do, check that table to get any other properties of interest.

If there's anything else you need, let me know. Otherwise, please mark this as answer :)

Answer 2

TFS 2005, it'll need to be [workitemtracking].dbo.[workitemsare]