Query to get all the Customfields containing a Default Value

[Sam S]

Hello Community,

we have too much Customfields. I must check the Fields, if there a default value included

is there any way to get it with a sql query or anything else?

thank you!

[Mark Zarich]

This worked for us, Jira 9.4.4 DC with MS SQL Server 2019 as the DB:

select cfname from yourschema.customfield cf where (CONCAT('customfield_',ID)) in (SELECT DISTINCT ct.customfield
FROM yourschema.configurationcontext ct
INNER JOIN yourschema.fieldconfigscheme AS fcs ON ct.customfield = fcs.fieldid
INNER JOIN yourschema.genericconfiguration gc ON fcs.id = gc.datakey
-- Field has a default value
WHERE gc.datatype = 'DefaultValue' AND ct.customfield LIKE 'customfield_%' and gc.xmlvalue not like '%10001%');

You can update the "yourschema." part as needed per your own DB config.

[Gaj Umapathy]

I had to do this recently so I will share the Postgres SQL which I used to get customfields with default values. The recent for this is I inherited a cloud instance which had default values and global context for several fields which had to be migrated to an on-prem instance so had to clean up before I do this.

{code}

select cname from customfield where customfield_id in (SELECT DISTINCT ct.customfield
FROM configurationcontext ct
INNER JOIN fieldconfigscheme fcs ON ct.customfield = fcs.fieldid
INNER JOIN genericconfiguration gc ON fcs.id::CHAR(60) = gc.datakey
-- Field has a default value
WHERE gc.datatype = 'DefaultValue' AND ct.customfield LIKE 'customfield_%' and gc.xmlvalue not like '%10001%');

{code}

SQL derived from https://confluence.atlassian.com/jirakb/how-to-check-which-custom-fields-have-global-contexts-and-default-values-and-aren-t-associated-with-any-screens-1072222991.html

Cheers,

Gaj

[Nic Brough -Adaptavist-]

I'm not sure having a default value is of much interest when you're trying to slim down your custom field list.

I would be looking for fields that have low usage, or similar names to others to see what you might merge.  The default value only really becomes useful after you have identified a field that might be worth killing off. 

For example if you spot a field called something like "type" and you suspect it might not be of any use, you run a search for "type is not empty".  If that returns 10,000 issues, then you might think "that could be a useful field", but if it has a default value, then you probably want to follow up with "type = default-value".  Because if that also returns around 10,000 issues, you know your users are simply accepting the default and the field is probably useless.

An SQL query to find this is likely to be hard work and not really help you cut down on fields anywhere near as quickly as just looking at the list of custom fields.