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 apps 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. Jira does not have a native feature for bulk exporting workflows in user-readable formats. Alternatively, you can manually export workflows' XML descriptors, use Python scripts with Jira REST API to transform the XML into readable formats, or explore third-party apps like 'Workflow Exporter for Jira.'
Environment
Compatible with most Jira versions; however, for optimal results and consistent support, ensure your Jira version is supported and up-to-date.
Solution
The following select statement will list all projects and their associated workflows:
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.
Note: If duplicate Resolution names occur after a migration from Jira Cloud to Data Center due to varying IDs, ensure resolutions are uniquely identified before running queries. Mapping these IDs and resolving duplicates can prevent inconsistencies during workflow or project analysis.
Was this helpful?