How to export list of all custom fields used in a project with their options
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
In this article we will discuss how we can list all available custom field with global context and custom field with project context for a specific project with associated field options.
Environment
Jira 8.x and later
Solution
Database schema for custom fields is well described here Database – Custom fields
The following query PostgreSQL provides a list of all available custom field with global context and custom field with project context for a specific project with associated field options. We tested it and appears working. It is not officially supported by Atlassian and requests to rewrite it for a specific DBMS may not be fulfilled.
In following query pkey is Project Key which is set to TES, it should be replaced by actual Project Key.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
SELECT COALESCE(pkey, '') AS pkey, COALESCE(pname, 'GLOBAL') AS Context, cc.customfield, cfname, COALESCE(customvalue, '') AS customvalue, COALESCE(disabled, '') AS disabled
FROM configurationcontext cc
LEFT JOIN project p
ON cc.project IS NOT DISTINCT FROM p.id
LEFT JOIN customfieldoption cfo
ON cfo.customfieldconfig = cc.fieldconfigscheme
INNER JOIN customfield cf
ON cc.customfield = CONCAT('customfield_', cf.id)where cc.project = (select id from project where pkey='TES') or (cc.project is null and not exists(select * from configurationcontext cc2
where cc2.project = (select id from project where pkey='TES') and cc2.customfield = cc.customfield) )ORDER BY context, cfname, customvalue asc
pkey|context |customfield |cfname |customvalue|disabled|
----+-----------+-----------------+---------------------+-----------+--------+
|GLOBAL |customfield_10000|Development | | |
|GLOBAL |customfield_10104|Epic Colour | | |
|GLOBAL |customfield_10101|Epic Link | | |
|GLOBAL |customfield_10103|Epic Name | | |
|GLOBAL |customfield_10102|Epic Status |Done |N |
|GLOBAL |customfield_10102|Epic Status |In Progress|N |
|GLOBAL |customfield_10102|Epic Status |To Do |N |
|GLOBAL |customfield_10110|Original story points| | |
|GLOBAL |customfield_10107|Parent Link | | |
|GLOBAL |customfield_10202|Proj3global | | |
|GLOBAL |customfield_10100|Rank | | |
|GLOBAL |customfield_10105|Sprint | | |
|GLOBAL |customfield_10106|Story Points | | |
|GLOBAL |customfield_10204|T1Single | | |
|GLOBAL |customfield_10109|Target end | | |
|GLOBAL |customfield_10108|Target start | | |
|GLOBAL |customfield_10111|Team | | |
|GLOBAL |customfield_10203|Test Dropdown |A |N |
|GLOBAL |customfield_10203|Test Dropdown |B |N |
|GLOBAL |customfield_10203|Test Dropdown |C |N |
|GLOBAL |customfield_10205|Test Dropdown 2 |1 |N |
|GLOBAL |customfield_10205|Test Dropdown 2 |2 |N |
|GLOBAL |customfield_10205|Test Dropdown 2 |3 |N |
TES |TestProject|customfield_10203|Test Dropdown |D |N |
TES |TestProject|customfield_10203|Test Dropdown |E |N |Was this helpful?