Is there a way to see all connected issue types to custom field?

[Inbar Levi]

At the custom fields screen (Jira admin), we can see the Type , Available Context(s) and Screens. Is there a way to see all connected issue types to custom field as well? 

I want to make order at the fields and connect each one of them to the right project and issue type via Context(s) options.

[Tom Lister · Accepted solution]

Hi @Inbar Levi

If you have access to you database try:

select 
p.pkey as project,
concat(p.pkey, '-',ji.issuenum) as issue,
it.pname as issuetype,
cf.cfname as customfield,
stringvalue, datevalue, numbervalue, textvalue
 from jiraissue ji, project p, customfield cf, customfieldvalue cfv, issuetype it
where ji.project = p.id
and cfv.issue = ji.id
and cfv.customfield = cf.id
and ji.issuetype = it.id

[Inbar Levi]

@Tom Lister 

Thanks! but I need the output of this to be based on issue type - not issue. can you help?

[Tom Lister]

Hi 

issuetype is returned so you can filter on that.

did you want a query based on the custom field contexts

[Inbar Levi]

@Tom Lister

Hey,

When I used your code, it returns all the data I have on my data base (28 GB) , its very hard to handle such big file.

 I need code that return only custom field -> linked to issuetype without duplicates (like select distinct at sql)

[Tom Lister]

I see what you mean

try with values and issues stripped out

select distinct
p.pkey as project,

it.pname as issuetype,
cf.cfname as customfield,
count(cf.cfname)
 from jiraissue ji, project p, customfield cf, customfieldvalue cfv, issuetype it
where ji.project = p.id
and cfv.issue = ji.id
and cfv.customfield = cf.id
and ji.issuetype = it.id
group by p.pkey, it.pname, cf.cfname

[Inbar Levi]

@Tom Lister

Thank you so much!! it worked!!!  :)

[aarti rajput]

I have tried this query but its not giving me any output, only header is there.

Any suggestion why so.

[Inbar Levi]

@Sreenivasaraju P Thanks but that is not what I need , I need to go over all custom fields and find what is the connected issue type/project and then insert it to the context(s) by myself . is there a way to find it easily? 

[Sreenivasaraju P]

@Inbar Levi,

Yes. You can see the configured issue types under custom field. 

Under available context(s), you can see the icons of the configured issue types

But the admin need to make sure they configure the issue types in their context or it will show as Issue type(s):Global (all issues)