SQL query to get unused custom fields is not working

[Vikrant Yadav]

Hi Guys,

Need your help in getting list of unused custom field. I have run the following SQL query but giving an error. Kindly suggest :-

Error :- The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.

Query Souce :- https://confluence.atlassian.com/enterprise/managing-custom-fields-in-jira-effectively-945523781.html

select count(*), customfield.id, customfield.cfname, customfield.description

from customfield left join customfieldvalue on customfield.id = customfieldvalue.customfield

where customfieldvalue.stringvalue is null 

and customfieldvalue.numbervalue is null 

and customfieldvalue.textvalue is null

and customfieldvalue.datevalue is null

group by customfield.id, customfield.cfname, customfield.description;

[Mohamed Benziane · Accepted solution]

Hi,

It seems that you're using a MSQL database so i think this threah might help you

https://stackoverflow.com/questions/14979413/the-text-ntext-and-image-data-types-cannot-be-compared-or-sorted-except-whe

In PGSQL the SQL is working fine.

[Vikrant Yadav]

Hi @Mohamed Benziane  I have tried to add as NVARCHAR but getting error :- Incorrect syntax near the keyword 'as'. 

select count(*), customfield.id, customfield.cfname, customfield.description

from customfield left join customfieldvalue on customfield.id = customfieldvalue.customfield

where customfieldvalue.stringvalue is null 

and customfieldvalue.numbervalue is null 

and customfieldvalue.textvalue is null

and customfieldvalue.datevalue is null

group by (customfield.id AS NVARCHAR(MAX)), (customfield.cfname AS NAVRCHAR(MAX)), (customfield.description AS NAVRCHAR(MAX));

[Vikrant Yadav]

Here is the working query for MySQL Database , which give the list of custom field which are EMPTY :- 

select count(*), customfield.id, customfield.cfname, cast([customfield].[description] as varchar(100)) as description from

customfield left join customfieldvalue on

customfield.id = customfieldvalue.customfield

where customfieldvalue.stringvalue is null

and customfieldvalue.numbervalue is null 

and customfieldvalue.textvalue is null 

and customfieldvalue.datevalue is null 

group by customfield.id, customfield.cfname, cast(customfield.[description] as varchar(100));

Thanks @Mohamed Benziane  After converting the datatype it works for me. 

V.Y

[Nic Brough -Adaptavist-]

Ah, the joys of running SQL against a Jira database.  It's a horrible thing to do.

I'm afraid your SQL won't work.  You're never going to get any results from it, even if it is correct.

You can't look for empty custom fields this way, because the data is not there, and you can't search for something that isn't there.

Customfieldvalue does not hold nulls for empty custom fields, it doesn't have a row at all.

Imagine I've got an issue with these fields set:

• Summary:  Mr Flibble says "time to die"
• Death date: 14 November 2022
• Actual death date: <empty>
• Method: Hex vision

Then my customfieldvalue table will be something like

ID	Issue	Customfield	string	number	text	date
10050	10678	10042				14/11/2022
10051	10678	10044	Hex Vision