TFS powershell and database projects – talk about an A-team!

We have previously discussed accessing work item information. It’s time to dig a bit deeper. Each Work Item has a Revisions collection which as you might expect, is a collection of Revision Objects. Each of these objects is a record in a work item history and contains a nullable Links property which (when not empty) can refer to a Link collection which contains Link objects which can represent a number of entities that a work item can be linked up to. Technically, link types can be user defined and any instance of a registered link type would do the job. It is worth noting that there are only four basic link types. The one that will connect us to the changed files is the External Link Type. Link Collection can also be accessed directly from the WorkItem object without going though revisions. For simplicity sake that’s what we will do in the example below.

Getting dizzy yet? Almost there. The gem that we’re after is called LinkedArtifactUri . it may look something like this – vstfs:///VersionControl/Changeset/123. It will take us straight to the changeset object associated with our work item and from there we can get to the actual files. Let’s see how this is actually going to work with powershell:

[string]$WorkItem="123"
[string]$tfsServer="http://defaultserver:8080/tfs"

$tfs=get-tfsserver $tfsServer

$WIT = $tfs.GetService([Microsoft.TeamFoundation.WorkItemTracking.Client.WorkItemStore])
$vCS = $tfs.GetService([Microsoft.TeamFoundation.VersionControl.Client.VersionControlServer])

So far so good. Things are going to get interesting now. Here is our links collection:

$WIT.GetWorkItem($WorkItem) |%{$_.Links}

but what we really want is the changeset object:

$changes=($WIT.GetWorkItem($WorkItem) |%{$_.Links} | ?{$_.LinkedArtifactUri -match "/Changeset/"} |  %{ $vCS.ArtifactProvider.GetChangeset($_.LinkedArtifactUri)} ) 

note that we’re only interested in links that are changesets. We could have also filtered based on BaseType property, but I find it’s not necessary. We could probably stop here and call it a day passing the changeset object to another script. But, what if you have changes in multiple DBs and you want to see the database name, object type, and object name? Too much to ask? Not if you using database projects. Thanks to the fact that DB objects are laid out in a predictable directory structure, you can easily get this information. First, get rid of the base path, second (optional) – the schema. In the example below I’m only looking at the programmable objects:

$FirstPart="$/MyProject/Branches/databases/"
$SecondPart="/Schema Objects/Schemas/dbo/Programmability"

$list=($changes | %{$_.Changes} |%{(($_.Item.ServerItem).Replace($FirstPart,"")).Replace($SecondPart,"")} |Sort-Object |Get-Unique).split('/')	

for ($i=0;$i -lt $list.Count;$i=$i+3){Write-Host $list[$i]"`t"$list[$i+1]"`t"$list[$i+2].split('.')[0]}

You can play with the step in the for loop to adjust it for the number of pieces of information that you want to get per file path. But long story short, with just few lines you get a clear picture of what objects changed in which database. For this example I queried a single work item, but you can of course use a WIQL query.