List of project using a particular issue type

Question

I need to know list of all projects in jira using a particular issue type.

Is there any SQL query for same

unknown · Accepted Answer

@Ron Jacob, you can find this information on the issue type scheme administration screen.

Here is a SQL query that will return the list of project for all issue types:

select IT.id, IT.pname, PROJ.name
from configurationcontext CC
join optionconfiguration OC on OC.fieldconfig = CC.fieldconfigscheme
join issuetype IT on IT.id = OC.optionid
join project PROJ on PROJ.id = CC.project
order by IT.pname;

Ipsita Dash_226ssg · Accepted Answer

Hi,

The below query worked for me. Give a try.

SELECT DISTINCT P.pname, P.pkey, P.lead AS project
FROM project as P
INNER JOIN jiraissue as J on J.PROJECT = P.ID
INNER JOIN issuetype as I on I.ID = J.issuetype 
WHERE J.issuetype= 10300

Thanks,

Ipsita Dash