Retrieve all custom field ids, names, field types, associated project names, associated issue types

Question

Hi,

I am trying to retrieve all custom field ids, names, field types, associated project names, associated issue types but not from the environment but from the database.

ok so in customfield table in the tables provided by jira, ID, name I can understand but for project & issuetype names and field types are giving "?" value as the SQL result

Can you help?

Jesse Wisener_5m6wsi · Answer

My app, Admin Pro for Jira makes all of this information easy to get at for all and allows you to export it all to CSV or Excel. It also does this for Jira objects--screens, workflows, priorities, all of their schemes, and more.

It also gives non-admin users a button on the Issue view to see all of the field IDs on the current issue so that they don't have to bug the admins for them.

Kimberly Blum_czyylu · Answer

This query includes the option values from 'select' type custom fields, but if you remove the join on cusomtefieldoption and the cfo.custsomevalue bit, you'll get all types.

SELECT DISTINCT  p.pkey, p.pname, cf.id cf_id, cf.cfname cf, SUBSTRING(customfieldtypekey,LOCATE(':',customfieldtypekey)+1,100) customfieldtype, COALESCE(cf.description,'') AS cf_descript, cfo.customvalue AS valueoption -- option if the field type is SelectFROM customfield cf JOIN customfieldvalue cfvON cf.id = cfv.customfieldJOIN customfieldoption cfo ONcf.id = cfo.customfieldJOIN jiraissue AS ji ON cfv.issue = ji.idJOIN project p ON p.id = ji.projectWHERE p.pkey = 'YOUR PROJECT HERE'

Nic Brough -Adaptavist- · Answer

Welcome to the Atlassian Community!

First obvious question is "why?"

Also, you've tagged this with Cloud, where you have no access to the database, so I'm going to assume you mean "Server" instead.

The field values you are seeing as "?" are either coming out because your SQL tool can't display the width of their data, can't read the encoding, or its binary.

Your best bet is to not read the database.

Even if you can get it to work, you do realise you could be getting hundreds of thousands of lines to analyse?

To determine if a field is associated with a project, you'll need to look at customfield, all the screens (and how they are associated with a project), the field configurations (and where they are associated with a project), and the field contexts.  You're making months of work for yourself here.