TFS 2017 How to query Work Items using SQL

Question

I'm searching for a way to query work items directly from the SQL database of TFS using SQL selects.

For TFS 2010, there were some database views you could use to achieve this goal. TFS2010: How to query Work Items using SQL on the Relational Warehouse

The example from the WIQ looks like this:

SELECT [System.Id], [System.WorkItemType], [System.Title], [System.AssignedTo], [System.State]
FROM WorkItems 
WHERE [System.TeamProject] = @project
  and [System.WorkItemType] = 'Ticket'
  and [System.State] <> 'Closed'
  and [System.State] <> 'Removed'

Answer 1

The best way I've found so far is to use the database view vw_denorm_WorkItemCoreLatest. In this case "Latest" means, you only get the latest revision of the workitem without any previous versions of it. If you need all versions of a workitem, use the view vw_WorkItemCoreAll.

Except the selected columns, this is as close as I got to the WIQ from my question:

SELECT *
FROM [dbo].[vw_denorm_WorkItemCoreLatest]
WHERE [System.TeamProject] = 'MyTeamProject'
  and [System.WorkItemType] = 'Ticket'
  and [System.State] <> 'Closed'
  and [System.State] <> 'Removed'