Retrieve Project Schemes and Categories for all Jira projects via the database
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
The Jira User interface does not provide an easy method of retrieving project schemes and settings. Currently, the View All Projects Page shows only their associated categories and lead information. We can query a list of this data, including the issue type scheme and permission scheme directly from the database.
Environment
8.x
Solution
The following query can be used to gather the details such as schemes and categories associated with a project.
Postgres
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
with cte as (select p.id as pid, p.pkey as prjkey,sink_node_id as AssocID, sink_node_entity as AssocScheme from project p join nodeassociation na on p.id = na.source_node_id and source_node_entity='Project')
select prjkey,prj.lead as Prjlead,cte.AssocScheme as Category,cname from project prj join cte on cte.pid=prj.id join ProjectCategory PC on cte.AssocID=PC.ID and cte.AssocScheme='ProjectCategory'
union
select prjkey,prj.lead as Prjlead,cte.AssocScheme as Category,NS.name from project prj join cte on cte.pid=prj.id join NotificationScheme NS on cte.AssocID=NS.ID and cte.AssocScheme='NotificationScheme'
union
select prjkey,prj.lead as Prjlead,cte.AssocScheme as Category,ISS.name from project prj join cte on cte.pid=prj.id join IssueSecurityScheme ISS on cte.AssocID=ISS.ID and cte.AssocScheme='IssueSecurityScheme'
union
select prjkey,prj.lead as Prjlead,cte.AssocScheme as Category,ITSS.name from project prj join cte on cte.pid=prj.id join IssueTypeScreenScheme ITSS on cte.AssocID=ITSS.ID and cte.AssocScheme='IssueTypeScreenScheme'
union
select prjkey,prj.lead as Prjlead,cte.AssocScheme as Category,WFS.name from project prj join cte on cte.pid=prj.id join WorkflowScheme WFS on cte.AssocID=WFS.ID and cte.AssocScheme='WorkflowScheme'
union
select prjkey,prj.lead as Prjlead,cte.AssocScheme as Category,PRS.name from project prj join cte on cte.pid=prj.id join PermissionScheme PRS on cte.AssocID=PRS.ID and cte.AssocScheme='PermissionScheme'
union
select prjkey,prj.lead as Prjlead,cte.AssocScheme as Category,FLS.name from project prj join cte on cte.pid=prj.id join FieldLayoutScheme FLS on cte.AssocID=FLS.ID and cte.AssocScheme='FieldLayoutScheme'
union
select prj.pkey as prjkey,prj.lead as Prjlead,INITCAP(fcs.fieldid)||'Scheme' as Category, fcs.configname from project prj join configurationcontext cct on cct.project=prj.id and customfield='priority' join fieldconfigscheme fcs on fcs.id=cct.fieldconfigscheme and fieldid='priority'
union
select prj.pkey as prjkey,prj.lead as Prjlead,INITCAP(fcs.fieldid)||'Scheme' as Category, fcs.configname from project prj join configurationcontext cct on cct.project=prj.id and customfield='issuetype' join fieldconfigscheme fcs on fcs.id=cct.fieldconfigscheme and fieldid='issuetype'
order by 1,3Sample Output as below. You can remove the union and select some of the categories you do not want in the above query.
1
2
3
4
5
6
7
8
9
10
11
prjkey | prjlead | category | cname
---------+---------------+-----------------------+-------------------------------------------------------------------------
SCRUM | JIRAUSER10000 | FieldLayoutScheme | Field Config Scheme for SCRUM
SCRUM | JIRAUSER10000 | IssueSecurityScheme | Tester
SCRUM | JIRAUSER10000 | IssueTypeScreenScheme | SCRUM: Scrum Issue Type Screen Scheme
SCRUM | JIRAUSER10000 | IssuetypeScheme | SCRUM: Scrum Issue Type Scheme
SCRUM | JIRAUSER10000 | NotificationScheme | Default Notification Scheme
SCRUM | JIRAUSER10000 | PermissionScheme | Default software scheme
SCRUM | JIRAUSER10000 | PriorityScheme | Test_Priority
SCRUM | JIRAUSER10000 | ProjectCategory | Linking_CatOne
SCRUM | JIRAUSER10000 | WorkflowScheme | SCRUM: Software Simplified Workflow SchemeWas this helpful?