screen_05kanbanBoard_Print

Kanban board is a nice feature for end users especially for its easy customization. However, it might not be that friendly for TFS Admin to provide custom reports. Here is a simple query to get all current column names for a purticular board/team.

SELECT c.DisplayPart
,c.NamePart 'Team Name'
,b.TeamId
,b.[Id] 'Board Id'
,wte.Id 'Field Id'
,f.FldID
,bc.Name 'Column Name'
,bc.[Order]
,bc.ColumnType
FROM [Tfs_DefaultCollection].[dbo].[tbl_Board] b

JOIN [Tfs_DefaultCollection].[dbo].[tbl_BoardColumn] bc
ON bc.BoardId = b.Id

JOIN [Tfs_DefaultCollection].dbo.Constants c
ON c.TeamFoundationId = b.TeamId

JOIN [Tfs_DefaultCollection].[dbo].[tbl_WorkItemTypeExtensions] wte
ON wte.OwnerId = c.TeamFoundationId

JOIN [Tfs_DefaultCollection].[dbo].[Fields] f
ON f.Name = 'WEF_' + Replace(wte.Id, '-', '') + '_Backlog items Column'

WHERE 1 = 1
AND b.CategoryReferenceName = 'Microsoft.RequirementCategory'
AND bc.RevisedDate = '9999-01-01 00:00:00.000'
AND c.NamePart = 'Kanban Board Name'
ORDER BY c.DisplayPart, bc.[Order]

Advertisements