How to retrieve all custom field configurations and custom field values in Jira Data Center from the 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
This article aims to assist in retrieving all custom field configurations and field values from the database in a Jira instance.
Environment
All currently supported Jira Server and Data Center versions
Diagnosis
This is not a native feature available in Jira and will need to be performed by querying the database directly.
Solution
Please note the following information is provided as-is since using SQL for business intelligence is beyond the scope of Atlassian Support.
Always back up your data before performing any modifications to the database. If possible, test any alter, insert, update, or delete SQL commands on a staging server first.
Retrieve the definitions of all custom fields configured in the Jira instance.
select cf.id, cf.cfname, cf.description, cfo.customvalue, cfo.disabled from customfield as cf join customfieldoption as cfo on cf.id = cfo.customfieldRetrieve all custom field value data for all issues in the Jira instance.
select CONCAT(p.pkey,'-',ji.issuenum) as issue_key, cf.cfname, cf.id as custom_field_id, cf.description, cfv.stringvalue, cfv.numbervalue, cfv.textvalue, cfv.datevalue, cfv.valuetype from customfield as cf join customfieldvalue as cfv on cf.id = cfv.customfield join jiraissue as ji on cfv.issue = ji.id join project p on p.id = ji.projectTo retrieve all issues to a specific custom field name (exclude select list field that has option values):
select CONCAT(p.pkey,'-',ji.issuenum) as issue_key, cf.cfname, cf.id as custom_field_id, cf.description, cfv.stringvalue, cfv.numbervalue, cfv.textvalue, cfv.datevalue, cfv.valuetype from customfield as cf join customfieldvalue as cfv on cf.id = cfv.customfield join jiraissue as ji on cfv.issue = ji.id join project p on p.id = ji.project where cf.cfname = 'CUSTOMFIELDNAME';To retrieve all issues from a select list field type of the existing options values:
select CONCAT(p.pkey,'-',ji.issuenum) as issue_key, cf.cfname, cf.id as custom_field_id, cf.description, cfo.customvalue, cfv.stringvalue, cfv.numbervalue, cfv.textvalue, cfv.datevalue, cfv.valuetype from customfield as cf join customfieldvalue as cfv on cf.id = cfv.customfield join customfieldoption as cfo on cfo.id = CAST (cfv.stringvalue AS INTEGER) join jiraissue as ji on cfv.issue = ji.id join project p on p.id = ji.project where cf.cfname = 'CUSTOMFIELDNAME';