Retrieve Project Schemes and Categories for all Jira projects via the database
Platform notice: Server and Data Center only. This article only applies to Atlassian products on the Server and Data Center platforms.
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.
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,3
Sample Output as below. You can remove the union and select some of the categories you do not want in the above query.
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 Scheme
For a more organized view of the data, you can make use of the crosstab in PostgreSQL and similar pivot functions in other databases. For Postgres, you need to enable the tablefunc extension by running the following in psql prompt
CREATE EXTENSION tablefunc;
Then proceed to run the following query to utilize the crosstab to transpose the columns to get an organized view of all the associated schemes and categories of a project
SELECT * FROM crosstab($$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,3$$,
$$
SELECT distinct sink_node_entity from nodeassociation where source_node_entity='Project'
union select 'PriorityScheme'
union select 'IssuetypeScheme' order by 1
$$)
AS final_result(ProjectKey TEXT, ProjectLead TEXT, FieldLayoutScheme TEXT, IssueSecurityScheme TEXT,IssueTypeScreenScheme TEXT,IssueTypeScheme TEXT,NotificationScheme TEXT,PermissionScheme TEXT,PriorityScheme TEXT,ProjectCategory TEXT,WorkflowScheme TEXT);
Here is the sample output
projectname | projectlead | fieldlayoutscheme | issuesecurityscheme | issuetypescreenscheme | issuetypescheme | notificationscheme | permissionscheme | priorityscheme | projectcategory | workflowscheme
-----------------+---------------+---------------------------------------------------------------+---------------------+--------------------------------------------------+-------------------------------------------+-----------------------------+------------------------------------------------------+------------------------------------------+-----------------+-------------------------------------------------------------------------
ADJAS | JIRAUSER10000 | | | ADJAS: Scrum Issue Type Screen Scheme | | Default Notification Scheme | Default software scheme | | | jdg-schema-for-project-adjamdjamensis
ADRYSA | JIRAUSER10000 | | | ADRYSA: Scrum Issue Type Screen Scheme | | Default Notification Scheme | Default software scheme | | Linking_CatTwo | jdg-schema-for-project-adryasa
CCERE | JIRAUSER10000 | | | CCERE: Scrum Issue Type Screen Scheme | | Default Notification Scheme | Default software scheme | | | jdg-schema-for-project-ccatherinae
CFORSA | JIRAUSER10000 | | | CFORSA: Scrum Issue Type Screen Scheme | | Default Notification Scheme | Default software scheme | | | jdg-schema-for-project-cfemoralisa
CTLBIA | JIRAUSER10000 | | | CTLBIA: Scrum Issue Type Screen Scheme | | Default Notification Scheme | Default software scheme | | | jdg-schema-for-project-ctullbergia
GBEI | JIRAUSER10000 | | | GBEI: Scrum Issue Type Screen Scheme | | Default Notification Scheme | Default software scheme | | | jdg-schema-for-project-gilbertii
ITSM | JIRAUSER10000 | Jira Service Desk Field Configuration Scheme for Project ITSM | | ITSM: Jira Service Desk Issue Type Screen Scheme | ITSM: Jira Service Desk Issue Type Scheme | Default Notification Scheme | Jira Service Desk Permission Scheme for Project ITSM | ITSM - Jira Service Desk Priority Scheme | | Jira Service Desk IT Support Workflow Scheme generated for Project ITSM
SCRUM | JIRAUSER10000 | Field Config Scheme for SCRUM | Tester | SCRUM: Scrum Issue Type Screen Scheme | SCRUM: Scrum Issue Type Scheme | Default Notification Scheme | Default software scheme | Test_Priority | Linking_CatOne | SCRUM: Software Simplified Workflow Scheme