Search code examples
t-sqltfskanban

TFS 2013 Kanban board Done column (multiple complete states)

In TFS 2013 Microsoft "fixed" a bug which allowed to map a WorkItem's state to the "Done" state in the Kanban board. This feature was heavily used in our company. There is a petition to bring it back back but I don't think it will make it: http://visualstudio.uservoice.com/forums/121579-visual-studio/suggestions/5589316-allow-multiple-complete-meta-state-mapping-in-tfs

In order to migrate TFS2012 to TFS2013 I would like to know where the customized "Done" state columns in TFS 2012 is stored in the database to create a report which shows which team used which WorkItem state as their "Done" state.

TFS2012 Kanban Board looked like that (note the dropdown): TFS2012 Kanban Board

TFS2013 Kanban Board looks like that (note NO dropdown): enter image description here

I do have access to the TFS Collection database and I would like to create a SQL query which shows me all the customization of this column in TFS 2012.

  • How can I get for "My WorkItem" the for every Team Project and every Team the customized "Done" state in TFS2012 database?
  • What other tables do I need to link to in order to get those states?

So far I could only get the TeamId, Name, ColumnType ProjectId but not the effective WorkItem and the "Done" column customization. How can I do that?

SELECT
    tbl_Board.TeamId, 
    tbl_Board.Revision, 
    tbl_BoardColumn.Name, 
    tbl_BoardColumn.ColumnType, 
    tbl_WorkItemTypeExtensions.Description, 
    tbl_BoardColumn.[Order], 
    tbl_WorkItemTypeExtensions.ProjectId
FROM
    tbl_WorkItemTypeExtensions
    RIGHT OUTER JOIN tbl_Board ON 
    tbl_WorkItemTypeExtensions.Id = tbl_Board.ExtensionId
    LEFT OUTER JOIN tbl_BoardColumn ON 
    tbl_Board.Id = tbl_BoardColumn.BoardId
Solution

  • I contacted the Microsoft Support and they provided me the following answer to my question:

    SELECT
       board.TeamId,
       boardColumn.Name,
       workItemTypeExtensions.Rules
FROM           
       tbl_Board board JOIN
       tbl_WorkItemTypeExtensions workItemTypeExtensions ON board.ExtensionId = workItemTypeExtensions.Id JOIN
       tbl_projects projects ON workItemTypeExtensions.ProjectId = projects.project_id  JOIN
       tbl_BoardColumn boardColumn ON board.Id = boardColumn.BoardId
WHERE       
       projects.project_name LIKE '%< ENTER YOUR PROJECT NAME HERE >%' AND
       boardColumn.ColumnType = 2
ORDER BY
       board.TeamId,
       boardColumn.[Order]

    When I check XML in the "Rules" column there I can find exactly what I was looking for.