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

Question

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

Gisela Lassahn · Answer

With this SQL I get:

So I tried it like this:

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

Mikel Garcia Bartolome · Answer

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 · Answer

@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.