TFS customization - Team Foundation Server question

Question

We have a process where when the developers change or add a database script and check it into the project. At the time of deployment the release manager needs to know what work items have database script checked in against it. Is there a way where we can query or create a custom report in TFS to get a list of work items that have a file in a changeset with a particular file extension (.sql). This way the release manager will get a list of the work items that she can then give to the DBAs to analyse, check and apply to the server.

We are using TFS 2008.

Answer 1

Install the latest Power Tools. Then you can run this quick Powershell script:

Get-TfsItemHistory $/project/*.sql -r -version D6/1/2009~ | 
    %{ $_.workitems } | %{ $_.id } | select -unique | sort

(adjust the project name and the date as needed)

While this is great for human-assisted code reviews, I would strongly caution against using this to actually construct the deployment script. If a developer forgot to associate his checkin with a work item, or if he made previous checkins to SQL files that his bugfixes depend on, you will be rolling out an inconsistent set of changes. It's always best to make the deploy script you use in your test environment match your production deployment as closely as possible.