SQL query to get list of custom fields in the server

[pradeep km]

Hi Community,

Can someone please provide us SQL query to get list of all used custom fields in the server.
If should contains below details
1) Custom field type.
2) Custom field name.
3)In how many projects it has been used.
4)It should contains whether custom field is duplicate or not.
5)It should contains complete data of the custom field(options, formula).
6) It should contains how many context created for custom field.

And also for all unused custom fields.

Regards,
Pradeep KM

[Nic Brough -Adaptavist-]

This is a horrid query to construct, as you'll need to join at least 9 tables together in a horrendously inefficient slow query.  The database is the last place you should be looking.

Can you explain what problem you think this report would help you solve?  It sounds like you want to do some housekeeping and simplification, but a monolithic report with this data in it is very unlikely to be of any help. (Especially on point 4 - there's no way the data can know it's a duplicate).  If it is housekeeping, then you should be analysing the data one function at a time.

[SriHarsha Kilari]

we need the total number of custom fields, for internal audit purpose. could you please give the query

[Nic Brough -Adaptavist-]

You'll get the wrong answer from SQL.  There are system fields implemented partially as custom fields, and the number won't be suitable for your audit.

Please, look in the admin interface, it won't take you long to count them up in there (6 pages at 25 per page, + 4 on the last page = 154), and it has a lot more (accurate) useful information than the SQL query will give you (take the system fields off the 154)

[pradeep km]

@Nic Brough -Adaptavist- 
 
We need this list for future reference even if someone delete custom fields.

[Nic Brough -Adaptavist-]

Use the audit log to see that.

[pradeep km]

Yes we are aware of audit logs.
But we need the data in excel 

[Nic Brough -Adaptavist-]

It sounds like you are trying to get audit data out, excel is a bad way to look at that sort of data.

What reporting are you actually trying to do?