Script to fetch all fields from a table which is not inherited

ShAn21 · ‎10-08-2020

HI All,

I have a table which is extended from CMDB CI. There are also many custom fields created in the child table.

Does anyone have a way to fetch the list all the custom fields from the child table via script?. it should only give results of non extended fields belonging to the child. It a huge table so I cannot check fields one by one.

I have done this before but somehow unable to get hold of the script again.

Thanks

Shreyas

Hitoshi Ozawa · ‎10-09-2020

My bad. The script worked on extended tables in cmdb but seems like it doesn't for custom tables.

Fixed the script.

var tableName = 'u_test_extend';

var tableUtil = new TableUtils(tableName);
var parentTables = tableUtil.getTables().toArray();
parentTables.shift();

var parentColumns = [];
for (var i=0; i<parentTables.length; i++) {
  var table = parentTables[i];
  var grSysParent = new GlideRecord('sys_dictionary');
  grSysParent.addEncodedQuery('name=' + table + '^internal_type!=collection^internal_typeISNOTEMPTY');
  grSysParent.addOrderBy('column_label');
  grSysParent.query();

  while(grSysParent.next()) {
    parentColumns.push(grSysParent.element.toString());
  }
}

var grSysCustom = new GlideRecord('sys_dictionary');
grSysCustom.addEncodedQuery('name=' + tableName + '^internal_type!=collection^internal_typeISNOTEMPTY');
grSysCustom.addOrderBy('column_label');
grSysCustom.query();

var customTableColumns = [];
while(grSysCustom.next()) {
  customTableColumns.push(grSysCustom.element.toString());
}

for (var j=0; j<parentColumns.length; j++) {
  var x = customTableColumns.indexOf(parentColumns[j]);
  if (x > -1) {
    customTableColumns.splice(x,1);
   }
}

gs.info(customTableColumns);

View solution in original post

Allen Andreas · ‎10-08-2020

Hi,

This post has script that will work. When querying the sys_dictionary table via GlideRecord and specifying the specific table...at that point, you'll only get fields that are derived from that table (so it will automatically not include fields that are extended).

https://community.servicenow.com/community?id=community_question&sys_id=5a8f738bdb1d17404837f3231f96...

Please mark reply as Helpful/Correct, if applicable. Thanks!

Please consider marking my reply as Helpful and/or Accept Solution, if applicable. Thanks!

ShAn21 · ‎10-09-2020

Hi Allen,

The script you mentioned no longer works for any custom extended tables.

Each table will have its own cloned descendent for each column. Try extending a new table from CMDB_CI and run this script. Please do let me know if you are successful.

Thanks

Shreyas

Allen Andreas · ‎10-09-2020

Hello,

I personally went in to my Paris instance, went to the sys_dictionary table, set the table I was interested in to "Incident" and 45 fields showed up. Of those 45, none were "State". State is extended from Task.

As you were wanting, you'd only want fields which derived from that table and were not extended. Once you've done that query, you can get all of the fields as needed. It does work, I've done it.

The script overall, is an example. Please review how it was done, then edit to your specifications.

Please do let me know if you are successful.

Thanks!

Please consider marking my reply as Helpful and/or Accept Solution, if applicable. Thanks!

SanjivMeher · ‎10-08-2020

You can also try creating a report, something like below, which should also give you all the custom fields from tables extended from cmdb_ci table.

Please mark this response as correct or helpful if it assisted you with your question.