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

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

This widget could not be displayed.

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.

I see thanks. See my updated question above.

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

This widget could not be displayed.

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.

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.

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

This widget could not be displayed.

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.

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.

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

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.

This widget could not be displayed.

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.

Suggest an answer

Log in or Sign up to answer
Atlassian Summit 2018

Meet the community IRL

Atlassian Summit is an excellent opportunity for in-person support, training, and networking.

Learn more
Community showcase
Posted Aug 06, 2018 in Jira Service Desk

A is for Activate: Share your top Jira Service Desk onboarding tips for new users!

Hi, everyone! Molly here from the Jira Service Desk Product Marketing Team :).  In the spirit of this month's  august-challenge, we're sourcing stories of Jira Service Desk activation fro...

580 views 25 15
Join discussion

Atlassian User Groups

Connect with like-minded Atlassian users at free events near you!

Find a group

Connect with like-minded Atlassian users at free events near you!

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you