Listing 6, Inside the TFS Databases, Part 2
- By Jeff Levinson
- 03/16/2009
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,
y.Name,
x.Words,
c.Rev
from WorkItemLongTexts x
join Fields y on x.FldID = y.FldID
join (select b.Name,
a.ID,
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="" b.name,="" a.id)="" c="" on="" c.name="y.Name" and="" c.rev="x.Rev" )="" --get="" the="" base="" work="" item="" select="" a.id,="" a.[team="" project],="" c.words="" as="" 'description',="" d.words="" as="" '[any="" other="" custom="" fields="" -="" match="" with="" join="" clause]',="" b.*="" from="" xxtree="" a="" join="" (select="" b.*="" from="" workitemswere="" b="" where="" b.id="@id" union="" all="" select="" null="" as="" 'revised="" date',="" a.*="" from="" workitemsare="" a="" where="" a.id="@id)" as="" b="" on="" a.id="b.AreaID" join="" tempdata="" c="" on="" b.id="c.ID" and="" c.name='Description' join="" tempdata="" d="" on="" b.id="d.ID" and="" d.name='[Any other custom fields - 1 per line]' where="" a.[team="" project]="@project" and="" b.id="@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 [email protected].