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.
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.
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;