To get the list of used projects template from database
Platform notice: Server and Data Center only. This article only applies to Atlassian products on the Server and Data Center platforms.
Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
Summary
When creating a new project there are some project templates available to choose from. After the project creation, you want to know how to get a list of the templates used by projects.
Environment
Jira Software 8.20.5 and 8.21.1.
Solution
There is a possibility to create a custom template as described in Creating a project template document and in many cases, we want to extract the list of the project using the custom template or other Jira standard template.
To get the templates used in the project you can run the below SQL statements to:
1. Find which is your template key:
select * from AO_B9A0F0_APPLIED_TEMPLATE;select * from "AO_B9A0F0_APPLIED_TEMPLATE";The result of the query will be a list of the templates available in your Jira:
+----+------------+------------------------------------------------------------------------+------------------------------------------------------------------------+
| ID | PROJECT_ID | PROJECT_TEMPLATE_MODULE_KEY | PROJECT_TEMPLATE_WEB_ITEM_KEY |
+----+------------+------------------------------------------------------------------------+------------------------------------------------------------------------+
| 1 | 10000 | com.pyxis.greenhopper.jira:gh-scrum-template | com.pyxis.greenhopper.jira:gh-scrum-template |
| 2 | 10100 | com.atlassian.jira-core-project-templates:jira-core-project-management | com.atlassian.jira-core-project-templates:jira-core-project-management |
| 3 | 10101 | com.atlassian.servicedesk:itil-v2-service-desk-project | com.atlassian.servicedesk:itil-v2-service-desk-project |
| 4 | 10102 | com.atlassian.servicedesk:itil-v2-service-desk-project | com.atlassian.servicedesk:itil-v2-service-desk-project |
| 5 | 10200 | com.example.plugins.tutorial.my-project-template:my-project-template | com.example.plugins.tutorial.my-project-template:my-project-template |
+----+------------+------------------------------------------------------------------------+------------------------------------------------------------------------+
5 rows in set (0,00 sec)2. Run the following SELECT statement providing the template key com.example.plugins.tutorial.my-project-template for instance:
select p.project_id, p.project_key, t.project_template_module_key from project_key p
left join AO_B9A0F0_APPLIED_TEMPLATE t on p.project_id=t.project_id
where t.project_template_module_key='com.example.plugins.tutorial.my-project-template:my-project-template';select p.project_id, p.project_key, t."PROJECT_TEMPLATE_MODULE_KEY" from "project_key" p
left join "AO_B9A0F0_APPLIED_TEMPLATE" t on p.project_id = t."PROJECT_ID"
where t."PROJECT_TEMPLATE_MODULE_KEY"='com.example.plugins.tutorial.my-project-template:my-project-template';Results:
The below result should return the list of the projects using the template com.example.plugins.tutorial.my-project-template:my-project-template:
+------------+-------------+----------------------------------------------------------------------+
| project_id | project_key | project_template_module_key |
+------------+-------------+----------------------------------------------------------------------+
| 10200 | TEM | com.example.plugins.tutorial.my-project-template:my-project-template |
+------------+-------------+----------------------------------------------------------------------+
1 row in set (0,00 sec)