CODEWITHSUNDEEP
sqldatabasetfsworkitem
Ferra76
Ferra76

Reputation: 61

Get history of work item with a script SQL directly from TFS database

For reasons that I will not tell you not to bore .. I need to directly access the database to extract the notes Feature. So I create this script to identify the projects i'm interested

SELECT *
  FROM [Tfs_GedCollection].[dbo].[tbl_WorkItemCoreLatest] a
INNER JOIN [Tfs_GedCollection].[dbo].[tbl_WorkItemCustomLatest] b ON a.PartitionId=b.PartitionId AND a.DataspaceId=b.DataspaceId AND a.Id = b.Id
  where a.[WorkItemType] = 'Feature'
    AND b.FieldId IN (
  -- CAMPO CODICEMMA
SELECT [FieldId] FROM [Tfs_GedCollection].[dbo].[tbl_Field]
 where [Tfs_GedCollection].[dbo].[tbl_Field].[Name] = 'CODICEMMA'-- Filed for identify projects in my company
  )

But I miss the last step: obtain the history (the discussion that is inserted through the control with type = WorkItemLogControl (mapped with the fieldname = System.History).

Thanks in advance

Upvotes: 0

Views: 2387

Answers (2)

Ferra76
Ferra76

Reputation: 61

I find the information that i need on the view: [vw_WorkItemComments] (in the column named [Text]).

So the query will be:



-- Identificazione dei progetti
SELECT b.stringvalue, c.[System.CreatedBy_IdentityDisplayPart], c.[System.ChangedDate], c.[Text]--, *
  FROM [Tfs_GedCollection].[dbo].[tbl_WorkItemCoreLatest] a
        INNER JOIN [Tfs_GedCollection].[dbo].[tbl_WorkItemCustomLatest] b ON a.PartitionId=b.PartitionId AND a.DataspaceId=b.DataspaceId AND a.Id = b.Id
        INNER JOIN [Tfs_GedCollection].[dbo].[vw_WorkItemComments] c ON a.Id = c.[System.Id]
  where a.[WorkItemType] = 'Feature'
    AND b.FieldId IN (
        -- CAMPO CODICEMMA
        SELECT [FieldId] FROM [Tfs_GedCollection].[dbo].[tbl_Field]
          where [Tfs_GedCollection].[dbo].[tbl_Field].[Name] = 'CODICEMMA'-- Filed for identify projects in my company
  )

Upvotes: 0

PatrickLu-MSFT
PatrickLu-MSFT

Reputation: 51083

It's not suggest to query the original database and also highly not recommend to modify the database data in TFS.

However, you can query for historical data about features, tasks, and other types of work items by using FactWorkItemHistory and the associated dimension tables shows in TFS data warehouse. More details please refer Work Item History tables

Another way is using TFS API to achieve this. How to achieve it please have a look at: TFS SDK: Work Item History Visualizer using TFS API

Upvotes: 3

Related Questions