2015:
SELECT c.DisplayPart
,c.NamePart ‘Team Name’
,b.TeamId
,b.[Id] ‘Board Id’
,wte.Id ‘Field Id’
,f.FieldID
,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].[tbl_Field] f
ON f.Name = ‘WEF_’ + Replace(wte.Id, ‘-‘, ”) + ‘_Backlog items Column’
OR f.Name = ‘WEF_’ + Replace(wte.Id, ‘-‘, ”) + ‘_Backlog items Column Done’
OR f.Name = ‘WEF_’ + Replace(wte.Id, ‘-‘, ”) + ‘_Extension Marker’

WHERE 1 = 1
–AND b.CategoryReferenceName = ‘Microsoft.RequirementCategory’
AND bc.RevisedDate = ‘9999-01-01 00:00:00.000’
AND c.NamePart = <Team/Board Name>
AND b.DataspaceId = <DataSpaceId>
ORDER BY b.[id],
f.FieldId, c.DisplayPart, bc.[Order]

===================================================================================

2013:
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 = <Team/Board Name>
ORDER BY c.DisplayPart, bc.[Order]

Advertisements