sql query to view data

[Sasmitha Chalamala]

Hi, which database table has the required ( 1 or  0 ) column for custom fields ? I checked the below tables, but they do not have this column that shows if a particular customfield is required or optional.

select * from customfield
select * from customfieldoption
select * from customfieldvalue

[Nic Brough -Adaptavist-]

I strongly recommend that you stop reading the database.  It's not designed for reporting or asking questions of, and it's a total mess when you're trying to find config data.

For field mandatory flags for example, you're going to have to join four tables together to get one way of doing it, and read xml and parse it from another set of three joined tables.

[Sasmitha Chalamala]

Thank you, can you suggest how to change a custom field from required to optional in all the forms at once in bulk (there are a lot of forms and cannot do manually for each form).

[Nic Brough -Adaptavist-]

You absolutely can not do this with the database - you will break it if you write to the database while Jira is running.

Also, you don't make fields mandatory or optional in the screens, so I think you might be looking at the wrong thing anyway.

If you don't want to do this in the UI, you'll need to get one of the scripting apps that would enable you to write something to bulk-change the field configuration schemes.

[Sasmitha Chalamala]

Hi, below highlighted shows where we make a field required or not. Is this not the right place? If so, can you please share the right place.

[Nic Brough -Adaptavist-]

Ah, ok, those are not issue fields in screens, they are request fields in the service desk portals.  They're done in yet another way.

You'll still want to get a scripting app if you want to bulk-change these.  

[Vishwas]

Hey @Sasmitha Chalamala 

My guess we have to check field config scheme table or field layout table to get the custom field required/option thing.

Check this kb which as an sql query there you can see the tables list in join query https://confluence.atlassian.com/jirakb/how-to-list-projects-and-field-configurations-by-issue-types-in-jira-1005782012.html

[Sasmitha Chalamala]

Thank you, but this sql did not show me the column I asked about. I even used the below where clause with all left joins, and did a select * to see all columns . Ran the sql during off-hours to avoid any issues.  

where fcs.fieldid in ('customfield_10302')