How can you tell if a Jira custom field is not used at all?

[dmitri]

Is this query sufficient to determine that a custom field is not currently used in any JIRA issues in an instance?

select * 
from customfield cf
where not exists 
	(select * from customfieldvalue cfv where cfv.CUSTOMFIELD = cf.ID)

And is this sufficient to see that it was never used historically?

select * 
from customfield cf
where not exists 
	(select * from customfieldvalue cfv where cfv.CUSTOMFIELD = cf.ID)
and not exists
	(select * from changeitem ci where ci.FIELDTYPE = 'custom' and ci.FIELD = cf.cfname)

[Nic Brough -Adaptavist- · Accepted solution]

No.  Because you're not looking at the history.  That query will show you if a field currently has data, but not if it had data and someone removed it later (that empties out the customfieldvalue line).  You can read changeitem for the name of the field to see where it was used.

[dmitri]

I see thanks. See my updated question above.

[Nic Brough -Adaptavist-]

Yep, that looks right to me   I don't use the database much, but that will catch all customfield usage.

[Chris Solgat]

Since there are so many avenues where a custom field can be referenced, we came up with a type of workaround to help identify used fields.  First we use the initial methods used here to identify "possible" non-used fields.  The "workaround" that we use is to simply rename the field in question.  Typically we just add a prefix to the original Field Name.  Usually that allows us to find out if it breaks any filters or projects without the problems that come from deleting the field and finding out afterwords.  This can also allow the field to be reused rather than deleted as well.

[dmitri]

Given my findings in https://answers.atlassian.com/questions/43719532 it would seem the above query is necessary but not sufficient to define 'is not used'. I found that some custom fields satisfy the above but also impact performance upon their removal. It would seem that they must be somehow used.

[Nic Brough -Adaptavist-]

Rank fields are different - once you've installed software, you'll find they have always been used.

For other custom fields though, it's not as simple as "data = load" as you seem to be implying.  A field has overhead, even if there has never been any data in it.

The earlier answers here are correct and valid for the question.  But performance of creation is a different matter to whether a field is filled or not.

[dmitri]

To clarify, in our instance the rank fields do not appear in the customfieldvalue table. 

[Nic Brough -Adaptavist-]

No, they won't, they're a different type of field.  I'm not sure what your question is here, as you're not looking at a standard custom field.

[Chris Solgat]

You will also need to check any of the saved filters to make sure that they are not referencing the field as well.  Not as critical, but will still cause any of those filters and/or dashboards to break.

[Nic Brough -Adaptavist-]

Good point.  Although the filters that get broken won't break more stuff - if someone tries to use one, they'll be told why its broken and if they own it, asked to correct it.  It won't cause problems past that.

[MattS]

And also quick filters and swimlanes in Agile boards. JQL gets embedded in a lot of places.