We often
find it difficult to find out which process template is used by the Team
Project, is it MSF Agile, Scrum or CMMI?
There are
some work around to find out what process template is used for the team
project, like default work item types or work items states, default reports in
report site or process guidance document which will be available in share point
site.
TFS provides
customization feature to enhance the out of the box capabilities. The Process
template customization is one such. We can perform the customizations either at
the process template level or at the individual team project level.
But when we
do customization for our team projects, there is possibility that we rename the
existing work item types, add new states or remove existing states, add or
remove reports etc. our team project might not have configured with share
point. In this situation it will be difficult to understand which process
template was used for creation of the team project?
The solution
is “tbl_project” table present in Collection DB which consists of columns like
“project_properties”. We slightly modified the queries given in the blog
to suite our requirement. Below is the modified query.
|
CREATE TABLE
#TB1_DBNAMES(ID INT IDENTITY(1,1), DBNAMES NVARCHAR(250))
CREATE TABLE
#TB1_PROJECTPROCESSTEMPLATE(COLLECTIONNAMES NVARCHAR(250),TEAMPROJECTNAMES
NVARCHAR(250),PROCESSTEMPLATES NVARCHAR(1000))
DECLARE @DBNAMES
VARCHAR(MAX)
SET @DBNAMES ='USE MASTER
INSERT INTO #TB1_DBNAMES
(DBNAMES) SELECT NAME FROM SYSDATABASES WHERE NAME LIKE ''Tfs_%''
and NAME NOT IN (''Tfs_Configuration'', ''Tfs_Warehouse'')'
EXECUTE (@DBNAMES)
DECLARE @COUNT INT = 0;
SET @COUNT = (SELECT
COUNT(ID) AS NOOFDBS FROM #TB1_DBNAMES)
DECLARE @LOOP INT = 1;
WHILE @LOOP <= @COUNT
BEGIN
DECLARE @DBNAME
VARCHAR(300);
DECLARE @SQL_SCRIPT
VARCHAR(MAX)
DECLARE @SQL varchar(max)
SET @DBNAME= (SELECT
DBNAMES FROM #TB1_DBNAMES WHERE ID = @LOOP)
SET @SQL ='use
'+@DBNAME+' INSERT INTO #TB1_PROJECTPROCESSTEMPLATE
(COLLECTIONNAMES,TEAMPROJECTNAMES,PROCESSTEMPLATES)
Select '''+@DBNAME+''',
tbl_projects.project_name as "Team Project",
tbl_project_properties.value as
"Process Template" from tbl_projects
inner join tbl_project_properties on
tbl_projects.project_id = tbl_project_properties.project_id
where tbl_project_properties.name like
''%Process Template%'''
EXECUTE (@SQL)
DECLARE @RESULT INT = 0
IF @RESULT = (SELECT
COUNT(*) FROM #TB1_PROJECTPROCESSTEMPLATE WHERE COLLECTIONNAMES = @DBNAME)
BEGIN
SET
@SQL = 'USE '+@DBNAME+' INSERT INTO #TB1_PROJECTPROCESSTEMPLATE
(COLLECTIONNAMES,TEAMPROJECTNAMES,PROCESSTEMPLATES)
SELECT
'''+@DBNAME+''',(SELECT project_name FROM [tbl_projects] where
[project_id]=[tbl_project_properties].project_id),
IIF(CHARINDEX(''CMMI'',
[value], 1)>0, ''CMMI'', IIF(CHARINDEX(''.SCRUM'', [value], 1)>0,
''SCRUM'', IIF(CHARINDEX(''.ESCRUM'', [value], 1)>0, ''ESCRUM'',
''AGILE'')))
FROM
[tbl_project_properties] where [name] like ''%MSPROJ%'' order by 1'
EXECUTE
(@SQL)
END
SET @LOOP=@LOOP+1
END
SELECT * FROM
#TB1_DBNAMES
SELECT * FROM
#TB1_PROJECTPROCESSTEMPLATE
DROP TABLE #TB1_DBNAMES
DROP TABLE
#TB1_PROJECTPROCESSTEMPLATE
Hope this helps. |
Informative, good article
ReplyDelete