How to find the projects associated to a particular custom field on Jira Data Center
Platform Notice: Data Center Only - This article only applies to Atlassian products on the Data Center platform.
Note that this KB was created for the Data Center version of the product. Data Center KBs for non-Data-Center-specific features may also work for Server versions of the product, however they have not been tested. Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
Summary
When working with JIRA, it might be necessary to identify projects that are using a specific custom field, particularly when the custom field is defined in a global context. However, the user interface limitations can make it challenging to fetch these projects, especially when the custom field is associated with a large number of issues.
Environment
8. x.x and further
Solution
To overcome this issue, you can execute specific SQL queries directly on your JIRA database. The following steps outline how to identify projects associated with a particular custom field:
Step 1: Identify the id of the custom field from the customfield table:
1
select id, cfname from customfield;Step 2: Check the data for this custom field in the configurationcontext table:
1
select * from configurationcontext where customfield like 'customfield_xxxxx'; Step 3: Replace xxxxx in customfield_xxxxx with the id accordingly (e.g., customfield_10200).
Step 4:
To identify any specific projects this customfield is associated with, you can run the following:
1
select * from configurationcontext cc left join project p on cc.project=p.id where customfield like 'customfield_xxxxx';However, when your custom field is set as global, you may not get the required results with these queries. In such cases, you can use the following queries to get the list of projects using this field (Replace
xxxxxwith your custom field id):
1
select distinct pname from project where id in (select distinct project from jiraissue where id in (select issue from customfieldvalue where customfield='xxxxx'));To get how many issues per project have a value for that custom field, use (Replace
xxxxxwith your custom field id):
1
select p.pname, count(*) from project p join jiraissue ji on ji.project = p.id join customfieldvalue cfv on cfv.issue = ji.id and customfield = 'xxxxx' group by p.pname;Please note that these queries can take a significant amount of time to execute, depending on the issue count associated with the custom field.
Important Note:
The provided queries are tested in PostgreSQL. Depending on the database you are using, you may need to modify the SQL syntax accordingly. If you're using a different DBMS like Oracle or MySQL, make sure to adjust the queries to match the SQL dialect of your database.
In the SQL queries above, replace "schema_name" with the actual schema name of your database setup. The schema name usually precedes the table name in SQL queries. If your Jira tables are not in the default public schema, or if you have multiple schemas in your database, ensure to use the correct schema name.
Was this helpful?