Finding empty fields in an table

[VigneshMC]

I have an table which contains close to 1000 records and there are about 600 fields in that table.I need to find the fields which are empty for all the records.Can anyone tel me easy/efficient way to do that?

[ahmeid]

This will print out which columns are empty and which are not:

var table = 'incident';

var gr = new GlideRecord(table);

gr.setLimit(1);

gr.query();

gr.next();

var fields = gr.getFields();

for (var i = 0; i < fields.size(); i++) {

        var field = fields.get(i).getName();

        var empty = isEmpty(table, field);

        gs.print('Column: ' + field + ' Empty: ' + empty);

}

function isEmpty(table, column) {

        var col = new GlideRecord(table);

        col.addNotNullQuery(column);

        col.setLimit(1);

        col.query();

        return !col.hasNext();

}

[zac]

Vignesh,

If this is a ServiceNow table:

• Then I agree with Ben that modifying the original table could create all kinds of problems you might not catch early on.
• Ben or Ahmed have great approaches to finding those fields

If this is a Datasource table:

• As you said, a table that wide is definitely not best practice, so I'd do some detailed analysis on the table
  • Get a count of non-null occurrences for each column (you may find that a field used 1-time is as bad as a field never used at all)
  • It's been a while since I've used SQL, but you can import your table into SQL (if it's not already there) and use CONCAT or GROUP_CONCAT
• Per Ben, you need to have a much better looking data model as your datasource
  • Create a new table taking into account your updated field requirements, normalization, etc
  • Export (or if in SQL SELECT INTO) the data into new table(s)
  • Find a way to decommission that old table

Good Luck

Zac

[VigneshMC]

Thanks guys for your reply

[abuthahir]

Use this.

//Find all incidents where the Short Description is empty

var gr = new GlideRecord('incident');

gr.addNullQuery('short_description');

gr.query();