Query for Work Items that contain attachments of a specific type

Question

In our releases sometimes it is necessary to run scripts on our production db. The standard has been to attach a .sql file to the work item if a script needs to be run on the db.

Is there any way I can query for Work Items which contain an attachment that is a .sql file? I'd prefer not to have to open each Work Item to check for these attachments each time I need to push a release.

Answer 1

This how I did it by querying the TfsWorkItemTracking database directly. I would assume that Fld10005 may or may not be the same on other instances of TFS. The fields can be found in the dbo.Fields table.

with [project-nodes] (
    ID,
    [Name],
    [Path],
    [Depth]) 
as (
    select 
        t.ID, 
        t.Name,
        cast(t.Name as varchar(max)) as [Path], 
        0 as [Depth]
    from dbo.TreeNodes t
    where t.ID = 220
    union all
    select 
        c.ID, 
        c.Name, 
        cast(p.[Path] + '/' + c.Name as varchar(max)),
        [Depth] + 1
    from dbo.TreeNodes c
    inner join [project-nodes] p
        on c.ParentID = p.ID)
select 
    t.[Path] as [Area Path], 
    l.Title, 
    l.Fld10005 as [Resolved Date], 
    f.OriginalName
from dbo.WorkItemsLatest l
inner join [project-nodes] t
    on l.AreaID = t.ID
inner join dbo.WorkItemFiles f
    on l.ID = f.ID
where f.OriginalName like '%.sql'
and l.Fld10005 > '2010-05-21' -- ResolvedDate
order by
    t.Name

Answer 2

Another way you could do this through WIQL, but still maintain some level of performance is to create a custom work item type that you would use for these tasks. Then, if you set up your WIQL query to be

(semi-pseudocode)

SELECT Id from WorkItems where 
WorkItemType = 'MySpecialWorkItem' AND
Status = 'Active';

You could then iterate through and check for the attached SQL files. Using the WorkItemType and Status criteria should limit the number of returned records significantly, then iterating through attachments won't affect performance of the process all that much.