How to get in Jira a list of workflow schemes per project and a count of projects per workflow scheme
Platform Notice: Data Center Only - This article only applies to Atlassian products on the Data Center platform.
Note that this KB was created for the Data Center version of the product. Data Center KBs for non-Data-Center-specific features may also work for Server versions of the product, however they have not been tested. 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
This article presents the option to retrieve the following information directly from the database using SQL queries:
a list of projects and their associated workflow schemes
a count of projects associated with each workflow scheme
Solution
The following select statement will list all projects and their associated workflow schemes:
1
2
3
4
5
SELECT p.pkey, wf.name FROM project p, workflowscheme wf, nodeassociation na
WHERE p.id = na.source_node_id
AND wf.id = na.sink_node_id
AND na.source_node_entity = 'Project'
AND na.sink_node_entity = 'WorkflowScheme';The following select statement gives a count of projects that are using each workflow scheme:
1
2
3
4
5
6
SELECT wf.name, count(p.pkey) FROM project p, workflowscheme wf, nodeassociation na
WHERE p.id = na.source_node_id
AND wf.id = na.sink_node_id
AND na.source_node_entity = 'Project'
AND na.sink_node_entity = 'WorkflowScheme'
GROUP BY wf.name;The queries were written and tested using a PostgreSQL DB, so you may need to tweak it depending on the database you are using.
Was this helpful?