How to find unused issue types with SQL
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
Problem
A Jira administrator may want to identify any issue types which are not being used across the instance. In other words, no issues of these issue types exist and therefore may be considered unnecessary clutter.
Solution
This query will return the name and id of any issue type which is currently not in-use.
1
2
3
4
5
6
SELECT pname, id
FROM issuetype
WHERE id
IN (SELECT optionid FROM optionconfiguration)
AND id
NOT IN (SELECT DISTINCT(issuetype) FROM jiraissue);It is also possible to restrict this to specific issue type schemes. For example, the query below only looks for issue types from the default issue type scheme.
1
2
3
4
5
6
SELECT pname, id
FROM issuetype
WHERE id
IN (SELECT optionid FROM optionconfiguration WHERE fieldconfig = 10000)
AND id
NOT IN (SELECT DISTINCT(issuetype) FROM jiraissue);Was this helpful?