What table or view can I get a Work Items Tags in

Question

I have a recent request to include Work ItemTag information in a daily SSRS report that I generate. I was led to the SQL query below. The problem is that I cannot find a table or a view with the "WorkItemsAre" in the name. So giving that I have the WorkItemSK, System_Id, ProjectSK and CollectionGUID among many other things, how and where can I get the tags querying directly against the TFS database (I know querying against the TFS databases is not recommended but It's in my requirements)?

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

Answer 1

TFS warehouse does not contain tags. For TFS 2018 (maybe also for tfs 2017) you can find information in table tbl_WorkItemCoreLatest in operational database (in my case: Tfs_DefaultCollection)

SELECT DISTINCT tbl_WorkItemCoreLatest.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 on tbl_WorkItemCoreLatest.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 tbl_WorkItemCoreLatest.Id = PROP_CNT.ArtifactId
) > 0