CODEWITHSUNDEEP
tfs
Cyndi Baker
Cyndi Baker

Reputation: 679

How do I query tags from the TFS database?

I am writing some custom SSRS reports for our QA teams and I need to add test case "Tags" to my query. It is my understanding these are not in the TFS_Warehouse. I have found dbo.tbl_TagDefinition in the operational store but cannot figure out how to join it to a work item.

Upvotes: 3

Views: 4030

Answers (3)

Sam Grantham
Sam Grantham

Reputation: 56

This is based off of @Robby's answer and still seems to work as of Azure DevOps 2019:

   select distinct 
       wcl.id, 
       td.Name from tbl_WorkItemCoreLatest wcl
left outer join tbl_PropertyValue pv on wcl.id = pv.artifactid
left outer join tbl_PropertyDefinition pd on pv.PropertyId = pd.PropertyId
left outer join tbl_TagDefinition td on pd.Name = ltrim(rtrim('Microsoft.TeamFoundation.Tagging.TagDefinition.' + cast(td.TagId as varchar(1000))))
where td.Name is not null

Upvotes: 3

Cece Dong - MSFT
Cece Dong - MSFT

Reputation: 31023

You can add a tag to work item query directly if your TFS contains the updates added with TFS 2013 Update 2 or later.

If you want to query from database, check this case:

TFS 2013 and up:

SELECT DISTINCT workItem.ID, tbl_TagDefinition.Name
    --,tbl_PropertyValue.ArtifactId, *
FROM tbl_TagDefinition
    LEFT JOIN tbl_PropertyDefinition ON tbl_PropertyDefinition.Name = 'Microsoft.TeamFoundation.Tagging.TagDefinition.' + CONVERT(NVARCHAR(400), tbl_TagDefinition.TagId)
    LEFT JOIN tbl_PropertyValue ON tbl_PropertyValue.PropertyId = tbl_PropertyDefinition.PropertyId
    --LEFT JOIN WorkItemLongTexts ON WorkItemLongTexts.ID = tbl_PropertyValue.ArtifactId
    left join tbl_WorkItemCoreLatest workItem on WorkItemsAre.ID = tbl_PropertyValue.ArtifactId
WHERE 
    (
       SELECT SUM(CASE WHEN IntValue = 0 THEN 1 ELSE -1 END) NB 
       FROM tbl_PropertyValue PROP_CNT 
       WHERE PROP_CNT.PropertyId = tbl_PropertyDefinition.PropertyId 
          AND workItem.ID = PROP_CNT.ArtifactId
    ) > 0

Prior to TFS 2013:

SELECT DISTINCT WorkItemsAre.ID, WorkItemsAre.Title, tbl_TagDefinition.Name
    --,tbl_PropertyValue.ArtifactId, *
FROM tbl_TagDefinition
    LEFT JOIN tbl_PropertyDefinition ON tbl_PropertyDefinition.Name = 'Microsoft.TeamFoundation.Tagging.TagDefinition.' + CONVERT(NVARCHAR(400), tbl_TagDefinition.TagId)
    LEFT JOIN tbl_PropertyValue ON tbl_PropertyValue.PropertyId = tbl_PropertyDefinition.PropertyId
    --LEFT JOIN WorkItemLongTexts ON WorkItemLongTexts.ID = tbl_PropertyValue.ArtifactId
    left join WorkItemsAre on WorkItemsAre.ID = tbl_PropertyValue.ArtifactId
WHERE 
    (
       SELECT SUM(CASE WHEN IntValue = 0 THEN 1 ELSE -1 END) NB 
       FROM tbl_PropertyValue PROP_CNT 
       WHERE PROP_CNT.PropertyId = tbl_PropertyDefinition.PropertyId 
          AND WorkItemsAre.ID = PROP_CNT.ArtifactId
    ) > 0

Upvotes: 5

Robby
Robby

Reputation: 29

The TFS 2015 db is convoluted, but here's how you query for a tag to find all it's matching work Items

select * from tbl_WorkItemCoreLatest
where
    id in
    (
        select CONVERT(INT, artifactid) from tbl_PropertyValue
        where
        PropertyId in
            (
                select PropertyId from tbl_PropertyDefinition 
                where 
                name in
                    (
                        select ltrim(rtrim('Microsoft.TeamFoundation.Tagging.TagDefinition.' + cast(tagid as varchar(1000)))) 
                            from tbl_TagDefinition 
                                where name like '%<TAG NAME TO SEARCH FOR>%'  -- Tag name you're looking for
                    )
            )
    )

Hope this helps :-)

TFS #TFS2015

-R

Upvotes: 2

Related Questions