Get all the fields used in particular project category from JIRA database using SQL

[Deleted user]

I have requirement I need to get list of all fields using in any screen, in any issue type for particular project category.

Instead of going to through each projects and screens, I need to have consolidate list of all the fields used in particular project category for maintenance purpose.

Any help is greatly appreciated.

[Niranjan · Accepted solution]

Hi @[deleted] ,

Here is the KB article that can help you - https://confluence.atlassian.com/jirakb/how-to-get-a-list-of-all-fields-in-a-project-967318426.html

RESTAPI call should give you the fields in the UI in json. You can use tools like excel or power PI to get the information in readable format.

http://jira.com:8080/rest/api/2/issue/createmeta?projectKeys=SPB&expand=projects.issuetypes.fields

[Deleted user]

Does not exactly answer my question, I need based on project category.

[Niranjan]

You cannot get it directly. Usage of category field to group projects with unrelated schemas is ineffective, especially if your teams needs category specific data.

You would need to tweak the DB query1 in the KB article.

Step 1 - Get the list of IssueTypeScreenSchema for all Projects (Modify the query to get it for projects that belong to category. You may need another join clause)

The remaining steps would be the same as given in KB article, once you identify the project to a particular category.

[Deleted user]

Problem is with the third query, does not fit with query 2

[Deleted user]

I will able to come this close, may be some one will get benefit from it

SELECT DISTINCT(fieldidentifier) FROM jir.fieldscreenlayoutitem
JOIN jir.fieldscreentab ON fieldscreentab.id=fieldscreenlayoutitem.fieldscreentab
JOIN jir.fieldscreen ON fieldscreen.id = fieldscreentab.fieldscreen
JOIN jir.fieldscreenschemeitem ON fieldscreenschemeitem.fieldscreen=fieldscreen.id
WHERE fieldscreenscheme IN
(SELECT fieldscreenscheme FROM jir.issuetypescreenschemeentity WHERE scheme in
(SELECT sink_node_id FROM jir.nodeassociation na
JOIN jir.project pr on pr.id=na.source_node_id
WHERE na.association_type = 'ProjectScheme' AND na.sink_node_entity = 'IssueTypeScreenScheme'
AND PR.PKEY in (SELECT p.pkey FROM JIR.project p
JOIN JIR.nodeassociation na ON p.id = na.source_node_id
JOIN JIR.projectcategory pc ON na.sink_node_id = pc.id
AND sink_node_entity = 'ProjectCategory'
and cname in ('PROJECT_CATEGORY_NAME')))
)
ORDER BY fieldidentifier;