Determining Process Template of an existing Team Project in TFS 2012


Following comes to us from Premal Ghelashah:

 

I have always had difficulty in identifying the process template used to create a team project. One way is to go to Portal settings for the team project. There will be a process guidance document which will have information related to the process template used to create the Team Project, but that is only available when you’ve integrated with SharePoint on your TP… but what if you haven’t?

Here’s a way to find out using a simple SQL Select:

SELECT [name], [value]
      FROM [Tfs_DefaultCollection].[dbo].[tbl_project_properties]
   where [name] like ‘%Process%’ and [project_id]=(SELECT [project_id]
 FROM [Tfs_DefaultCollection].[dbo].[tbl_projects]
 where project_name like ‘%<put project name here>%’)

 

This implies you can access the TFS back-end SQL DB of course. If you can’t, ask your admin for help. 🙂


Comments (14)

  1. Bijendra Kumar says:

    I Tried it with below query- it was executed successfully, but Zero row was affected…did not get any information.

    SELECT [name], [value]

         FROM [Tfs_DefaultCollection].[dbo].[tbl_project_properties]

      where [name] like '%Process%' and [project_id]=(SELECT [project_id]

    FROM [Tfs_DefaultCollection].[dbo].[tbl_projects]

    where project_name like '%TestProject%')

  2. Trevor says:

    Both Premal and I have run this in SQL 2008 R2 used for TFS 2012 and it works. Here's another one – just change the collection name in the first line. It will list the projects in it, and the process template they use:

    USE Tfs_DefaultCollection

    Select 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%'

  3. Michel Zehnder says:

    Hey Trevor

    This does not seem to work on TFS 2013.

    We only have these names in the tbl_prject_properties table:

    Microsoft.TeamFoundation.Team.Default

    MSPROJ

    No "Process Template" unfortunately 🙁

  4. I'll look into this tomorrow during office hours and loop back.

  5. Boris says:

    I tried in TFS 2013 and the following query does work:

    Select 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%'

  6. Eoin Leonard says:

    Trevor any update here for 2013 specifically 2013.3

  7. melgoth says:

    Hi!

    I have tried the query too and the result ist just a subset of all templates which were uploaded to TFS 2013.

    <select * from tbl_ProcessTemplateDescriptor> get 'all' (I think so) templates, but how is the relation to the project(s)?

  8. Gouthami Manganoor says:

    in TFS 2013, We can check process template used to create the Team Project as below:

    Go to Team Explorer -> Documents -> Process Guidance , then open ProcessGuidance.html. it will tell us which template used to create that team project.

  9. Srinivas reddty says:

    I also couldn't see any results from the above queries

  10. J. Bennink says:

    Really old thread, I wondered the same thing. I have the same problem as Michel Zahnder had. It might be because he uses a collection that was upgrade from an older version of TFS. We use 2010 at the moment but our collection was upgraded (moved) from a previous TFS installation that was even older.

  11. Rizwan Zubairy says:

    You can try something in TFS 2013 using the XML data field like:

    SELECT (SELECT project_name

    FROM [Tfs_DefaultCollection].[dbo].[tbl_projects]

    where  [project_id]=[Tfs_DefaultCollection].[dbo].[tbl_project_properties].project_id) as pname,

    IIF(CHARINDEX('CMMI', [value], 1)>0, 'CMMI', IIF(CHARINDEX('.SCRUM', [value], 1)>0, 'SCRUM', IIF(CHARINDEX('.ESCRUM', [value], 1)>0, 'ESCRUM', 'AGILE'))) AS ProcessType

         FROM [Tfs_DefaultCollection].[dbo].[tbl_project_properties]

      where [name] like '%MSPROJ%'

       order by 1

  12. Premal says:

    If you are using TFS 2013 then query needs to be modified as database name for TFS DB and table names are changed little bit in TFS 2013. Below query works perfectly fine for me in TFS 2013 server.

    In below query Tfs_Projects is my collection name so TFS database got created by name Tfs_Projects. If your project collection name is DefaultCollection then it would be Tfs_DefaultCollection. If your collection name is ABCXYZ then TFS database would be Tfs_ABCXYZ. So you have to make small change in below query as per your TFS collection name.

    SELECT [name], [value]

         FROM [Tfs_Projects].[dbo].[tbl_project_properties]

      where [name] like '%Process%' and [project_id]=(SELECT [project_id]

    FROM [Tfs_Projects].[dbo].[tbl_Project]

    where ProjectName like '%<your TFS project name>%')

  13. Wayne says:

    Yupp, does not work with TFS 2013. Also Rizwan Zubairy does only provide some hint whether the template ist CMMI, Scurm or Agile. But not witch version. Select works with TFS 2013, but I need the exact version