Identify custom fields without values per issue type and project
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
When performing a merge of multiple Jira instances, migrating projects, or just implementing a routine to brush off unused custom fields, it is useful to see exactly which custom fields are actually needed or used on the project. It is common to have fields added to screens and therefore to issues, that never are used.
Having unused custom fields added to issues will add up time to create and update those issues on every user action. We also recommend that you verify the following documentation to optimize custom fields in your Jira instance: https://confluence.atlassian.com/adminjiraserver/optimizing-custom-fields-956713279.html .
Solution
You can use the query below to retrieve all the custom fields which are added to screens of a given issue type and of a specific project and don't have any data actually added to them on the issues. With the result from this query, you may consider removing these custom fields from your screens.
⚠️ Do not remove/delete the custom field from the whole instance as they might still be used by other projects;
⚠️ We suggest that you only remove the custom field from the screen and later from the issue type scheme, and not remove the custom field from the instance;
⚠️ We recommend using a JQL to confirm that you don't see any data for that given custom field on Jira UI. The query output should be considered as information only and not as a source of truth;
SQL Query
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
SELECT
F.NAME as "Screen Name"
,L.FIELDIDENTIFIER AS "Field Identifier"
,C.cfname as "Field name"
from jiraissue ji
join project on project.id = ji.project
JOIN nodeassociation na ON na.SOURCE_NODE_ID = project.ID and na.SOURCE_NODE_ENTITY = 'Project'
JOIN issuetypescreenscheme itss ON itss.ID = na.SINK_NODE_ID and na.SINK_NODE_ENTITY = 'IssueTypeScreenScheme'
JOIN issuetypescreenschemeentity itsse ON itsse.SCHEME = itss.ID
JOIN fieldscreenscheme fss ON fss.ID = itsse.FIELDSCREENSCHEME
JOIN fieldscreenschemeitem fsi ON fsi.FIELDSCREENSCHEME = fss.ID
JOIN fieldscreen F ON f.ID = fsi.FIELDSCREEN
JOIN customfieldvalue cfv on cfv.issue=ji.id
JOIN fieldscreentab T ON F.ID = T.FIELDSCREEN
JOIN fieldscreenlayoutitem L ON T.ID = L.FIELDSCREENTAB
join customfield C ON SUBSTRING(L.fieldidentifier,13,99) = C.ID::varchar(10) and c.id = cfv.customfield
WHERE project.pkey = '<PROJECT_KEY>'
and (cfv.stringvalue is not null or cfv.textvalue is not null or cfv.numbervalue is not null)
group by 1,2,3ℹ️ Replace <PROJECT_KEY> on the query above with the project key that you want to obtain results.
Was this helpful?