Query for work Items containing attachments of a certain type

Question

In our releases, sometimes you need to run scripts on our db production. The standard was to attach the .sql file to the work item if you need to run the script in db.

Is there any way to request Work items containing an attachment that is an .sql file? I would prefer not to open every work item in order to check these attachments every time I need to press release.

Answer 1

This is how I did it by directly querying the database TfsWorkItemTracking. I would suggest that it Fld10005may or may not be the same in other instances of TFS. Fields can be found in the table dbo.Fields.

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

, WIQL, , - , . , WIQL

(-)

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

SQL. WorkItemType Status , .