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

[Sharad Goyal]

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?

[Nic Brough -Adaptavist-]

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.

[Kimberly Blum]

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 Select

FROM customfield cf 

JOIN customfieldvalue cfv

ON cf.id = cfv.customfield

JOIN customfieldoption cfo ON

cf.id = cfo.customfield

JOIN jiraissue AS ji ON cfv.issue = ji.id

JOIN project p ON p.id = ji.project

WHERE p.pkey = 'YOUR PROJECT HERE'