Need to get a list of Projects, their Issue Type Schemes and Issue Types from the database

[Gisela Lassahn]

Hi everyone,

I'm trying to produce an overview over all our projects, their schemes and much more, and I've already found out a lot, but I don't know this:

How do I get the information which Issue Types are available in which projects from the database (SQL Server)?

I want to produce a list with the following columns:

• Project-ID
• Project-Key
• Issue Type Scheme ID
• Issue Type Scheme Name
• Issue Type ID
• Issue Type Name

At the moment my SQL looks like this:

SELECT
JIRADB.jiradb.project.id as pid ,
JIRADB.jiradb.project.pkey as pshortcut ,
JIRADB.jiradb.fieldconfigscheme.ID as issueTypeSchemeID ,
JIRADB.jiradb.fieldconfigscheme.configname as issueTypeSchemeName
FROM JIRADB.jiradb.project
JOIN JIRADB.jiradb.configurationcontext
ON JIRADB.jiradb.configurationcontext.PROJECT = JIRADB.jiradb.project.ID
JOIN JIRADB.jiradb.fieldconfigscheme
ON JIRADB.jiradb.fieldconfigscheme.ID = JIRADB.jiradb.configurationcontext.FIELDCONFIGSCHEME
WHERE JIRADB.jiradb.fieldconfigscheme.FIELDID = 'issuetype'

But where do I get the Issue Type ID and Name from?

We're running on Jira Datacenter 9.4.8 and are currently in the process of updating to 9.17.2.

(Unfortunately my formatting has been lost.)

Many thanks and best regards,
Gisela

[Mikel Garcia Bartolome]

Hey @Gisela Lassahnto get the Issue Type ID and Issue Type Name in addition to the information you've already gathered, you need to join your existing SQL query with the table that contains issue type definitions. In Jira, this information is stored in the `issuetype` table.

Here’s an updated version of your SQL query that adds the Issue Type ID and Issue Type Name columns:

SELECT
 proj.id AS pid,
 proj.pkey AS pshortcut,
 fieldconfigscheme.ID AS issueTypeSchemeID,
 fieldconfigscheme.configname AS issueTypeSchemeName,
 issuetype.id AS issueTypeID,
 issuetype.pname AS issueTypeName
FROM
 jiradb.project AS proj
JOIN
 jiradb.configurationcontext AS configcontext
 ON configcontext.PROJECT = proj.ID
JOIN
 jiradb.fieldconfigscheme AS fieldconfigscheme
 ON fieldconfigscheme.ID = configcontext.FIELDCONFIGSCHEME
JOIN
 jiradb.optionconfiguration AS optconfig
 ON fieldconfigscheme.ID = optconfig.FIELDID
JOIN
 jiradb.issuetype AS issuetype
 ON optconfig.OPTIONID = issuetype.ID
WHERE
 fieldconfigscheme.FIELDID = 'issuetype';

Explanation of Key Joins

• `jiradb.issuetype`: This table contains the Issue Type ID (`id`) and Issue Type Name (`pname`).
• `jiradb.optionconfiguration`: This table links the fieldconfigscheme to specific issue types (via `OPTIONID`).

You can adjust the specific table and column names based on your database schema, as Jira databases can vary slightly depending on custom configurations and versions.

[Gisela Lassahn]

@Mikel Garcia Bartolome 
Many thanks! :-)

But when trying to execute your SQL, I get:

Msg 8114, Level 16, State 5, Line 1
Error converting data type nvarchar to numeric.

[Mikel Garcia Bartolome]

In the SQL query, it seems the field jiradb.optionconfiguration.OPTIONID might be a nvarchar (string), and jiradb.issuetype.ID is numeric. SQL Server might be implicitly trying to convert one field to match the other.

First check the data types:

SELECT COLUMN_NAME, DATA_TYPE 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = 'optionconfiguration' AND COLUMN_NAME = 'OPTIONID';

SELECT COLUMN_NAME, DATA_TYPE 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = 'issuetype' AND COLUMN_NAME = 'ID';

If OPTIONID is nvarchar and issuetype.ID is numeric, you can cast OPTIONID to a numeric type for the join. This assumes that OPTIONID contains valid numeric data.

SELECT
 proj.id AS pid,
 proj.pkey AS pshortcut,
 fieldconfigscheme.ID AS issueTypeSchemeID,
 fieldconfigscheme.configname AS issueTypeSchemeName,
 issuetype.id AS issueTypeID,
 issuetype.pname AS issueTypeName
FROM
 jiradb.project AS proj
JOIN
 jiradb.configurationcontext AS configcontext
 ON configcontext.PROJECT = proj.ID
JOIN
 jiradb.fieldconfigscheme AS fieldconfigscheme
 ON fieldconfigscheme.ID = configcontext.FIELDCONFIGSCHEME
JOIN
 jiradb.optionconfiguration AS optconfig
 ON CAST(optconfig.OPTIONID AS NUMERIC) = issuetype.ID
JOIN
 jiradb.issuetype AS issuetype
 ON optconfig.OPTIONID = issuetype.ID
WHERE
 fieldconfigscheme.FIELDID = 'issuetype';

 Try applying the solution and let me know if it works :)

[Gisela Lassahn]

With this SQL I get:



So I tried it like this:


But as you can see, the result is not like I had expected.