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

0 vote

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.

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.

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.

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 Join to answer
Community showcase
Sarah Schuster
Posted Jan 29, 2018 in Jira

What are common themes you've seen across successful & failed Jira Software implementations?

Hey everyone! My name is Sarah Schuster, and I'm a Customer Success Manager in Atlassian specializing in Jira Software Cloud. Over the next few weeks I will be posting discussion topics (8 total) to ...

3,293 views 14 20
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
Atlassian Team Tour

Join us on the Team Tour

We're bringing product updates and pro tips on teamwork to ten cities around the world.

Save your spot