How to retrieve a list of workflows associated with each project in Jira Data Center using SQL queries
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 provides a method to retrieve a list of workflows per project directly from the database using SQL queries.
Environment
Any Jira version.
Solution
The following select statement will list all projects and their associated workflows:
1
2
3
4
5
6
7
8
9
10
SELECT p.id AS project_id,
p.pkey AS project_key,
p.pname AS project_name,
ws.name AS workflow_scheme_name,
wse.workflow AS workflow_name
FROM project p
LEFT OUTER JOIN nodeassociation na ON na.source_node_id = p.id AND na.sink_node_entity = 'WorkflowScheme'
LEFT OUTER JOIN workflowscheme ws ON ws.id = na.sink_node_id
LEFT OUTER JOIN workflowschemeentity wse ON wse.scheme = ws.id
ORDER BY p.pkey;ℹ️ The query above was written for PostgreSQL and may need syntax changes depending on your database.
Was this helpful?