Listing 5, Inside the TFS Databases, Part 2

DECLARE @rev int = [A specific revision number];
DECLARE @id int = [A specific work item id];
DECLARE @project varchar(255) = [A specific team project name];

with tempData as
--Get the long text values	
select  x.ID,
from	WorkItemLongTexts x
join	Fields y on x.FldID = y.FldID
join	(select  b.Name,
		 max(a.Rev) as Rev
	 from	 WorkItemLongTexts a
	 join	 Fields b on a.FldID = b.FldID
	 where	 a.ID = @id
		 and a.Rev <= @rev="" group="" by="",="""" c="" on="""y.Name" and="" c.rev="x.Rev" )="" --get="" the="" base="" work="" item="" select="",="" a.[team="" project],="" c.words="" as="" 'description',="" d.words="" as="" '[any="" other="" custom="" fields="" -="" match="" with="" join="" clause]',="" b.*="" from="" xxtree="" a="" join="" workitemswere="" b="" on="""b.AreaID" join="" tempdata="" c="" on="""c.ID" and=""'Description' join="" tempdata="" d="" on="""d.ID" and=""'[Any other custom fields - 1 per line]' where="" a.[team="" project]="@project" and="""@id" and="" b.rev="@rev">

About the Author

Jeff Levinson is the Application Lifecycle Management practice lead for Northwest Cadence specializing in process and methodology. He is the co-author of "Pro Visual Studio Team System with Database Professionals" (Apress 2007), the author of "Building Client/Server Applications with VB.NET" (Apress 2003) and has written numerous articles. He is an MCAD, MCSD, MCDBA, MCT and is a Team System MVP. He has a Masters in Software Engineering from Carnegie Mellon University and is a former Solutions Design and Integration Architect for The Boeing Company. You can reach him at

comments powered by Disqus
Upcoming Events

.NET Insight

Sign up for our newsletter.

I agree to this site's Privacy Policy.