How to identify unused values in a custom field

Question

Is there a way via API or SQL DB query that we can identify the list values of a select list customfield that have not been used in any JIRA issues?

April · Answer

I have the same thing, so I use this with MSSQL:

select * 
from jiraschema.customfieldoption c
where CUSTOMFIELD = '10000' -- Field ID
and not exists 
(select 1 from jiraschema.customfieldvalue c2
where CUSTOMFIELD = '10000' -- Field ID
and c2.STRINGVALUE = c.ID)
order by ID

Although the field has ~ 5,000 options and my instance is largish, this method returns quickly. Good luck!

Jeremy Upright · Answer

Thanks @Jack Nolddor [Sweet Bananas] for the reply!  I was aware of this; however, I have a select list field that contains >1000 options, so clicking through each one would be time consuming.  I was hoping for a more programmatic way of doing this either via API or SQL query possibly?

Jack Nolddor [Sweet Bananas] · Answer

Hi Jeremy Uprigth,

For checkboxes and dropdowns fields, the tool itself tell you how many issues there are with specific value if you try to remove it from the available options (Jira administrator global permission is needed to perform this action).

See the image below:

Does it solve your problem?
Regards