How to check which custom fields have global contexts and default values and aren't associated with any screens
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
Purpose
Custom fields, when not used wisely and not governed properly, can put a lot of strain on Jira's performance.
The good news is that you can be proactive with custom fields. Checkout Managing custom fields in Jira effectively to know how.
If you're thinking of optimizing custom fields, checkout Optimizing custom fields.
Since the custom field optimizer doesn't check for the existence of the fields in screens or whether or not the fields have default values, the query below may come in handy.
Solution
The query below was written by Atlassian Support on best-effort basis for PostgreSQL. It is not officially supported by Atlassian and requests to rewrite it for a specific DBMS may not be fulfilled.
The query will return custom fields info and the number of tickets this custom field is set in.
Once you get the result of the query, make sure you validate that the fields reported do indeed satisfy all those conditions and proceed with the cleanup actions (removing default values, creating project-based contexts, etc...)
SELECT cf.id, cf.cfname, COUNT(cfv.issue)
FROM customfield cf
LEFT JOIN customfieldvalue cfv ON cf.id = cfv.customfield
WHERE cf.id IN
(
SELECT DISTINCT SUBSTRING(ct.customfield, 13)::INTEGER
FROM configurationcontext ct
INNER JOIN fieldconfigscheme fcs ON ct.customfield = fcs.fieldid
INNER JOIN genericconfiguration gc ON fcs.id::CHAR(60) = gc.datakey
-- Field has a default value
WHERE gc.datatype = 'DefaultValue'
AND ct.customfield LIKE 'customfield_%'
-- Context is global
AND ct.project IS NULL
-- Field is not present in any screen
AND ct.customfield NOT IN (SELECT fieldidentifier
FROM fieldscreenlayoutitem)
)
GROUP BY cf.id;