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

dmitri October 17, 2016

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)

4 answers

1 accepted

0 votes
Answer accepted
Nic Brough -Adaptavist-
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
October 17, 2016

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 October 17, 2016

I see thanks. See my updated question above.

Nic Brough -Adaptavist-
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
October 17, 2016

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

1 vote
Chris Solgat
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
October 28, 2016

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.

0 votes
dmitri October 27, 2016

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-
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
October 27, 2016

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 October 27, 2016

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

Nic Brough -Adaptavist-
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
October 27, 2016

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.

0 votes
Chris Solgat
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
October 17, 2016

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-
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
October 17, 2016

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
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
October 17, 2016

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

Suggest an answer

Log in or Sign up to answer