How to find out custom fields mapped to a project

[Lokesh D]

Usually we encounter a question what are the customfields mapped to a project, although there are manual/time-taking ways to get this info. Is there a way by querying database or by any other querying method by which this can be retrieved ?

[Fabrizio Galletti]

http://confluence.atlassian.com/display/JIRA040/Database+Schema

Some information about database schema.

[Nic Brough -Adaptavist-]

Not really, the UI is the most friendly way of finding them out, because the data is complex. An SQL query would give you the right answer, but in an even less friendly format than the UI.

[Lokesh D]

Is there an sql query that would provide list of all Custom as well as system fields mapped to a project. This would be much helpful in cases where more than thousand custom fields & hundreds of projects exists. Particularly in Custom Fields page, it takes a while to scroll-up/down & refreshing takes a time

[Nic Brough -Adaptavist-]

Well, I'm sure an SQL expert could do it, given the schema, but it would be complex and messy.

First, you need to separate out "system" and "custom" fields.

System fields are more simple - they automatically belong to all projects. The complexity is finding out whether they are used. To do that, you'd need to check the field configuration to see if they're enabled, and then the screens in use to see if they are used (note - you've got multiple screens - View, Edit, Create and all the potential transitions). This means you'd need project -> field configuration scheme -> field configuration -> field, then a scan through the project -> issue type screen scheme -> screen schemes -> screens and then read the workflow to look for screens too.

Custom fields have the same complexity, but then you need to consider field context for project/issue type on top of them too.

So, yes, it's possible. But I *seriously* wouldn't bother trying to do it in SQL