ServiceNow REST API: Get list of column names

Question

From the admin UI, there is a Tables and Columns explorer that dutifully shows all available columns for a table, such as the incident table:

My ultimate goal is to be able to query all fields for a given table that I can insert data to (mostly centered around incident and problem tables), match that against what data I have, and then insert the record with a PUT to the table. The immediate problem I am having is that when I query sys_dictionary as various forums suggest, I only get returned a subset of the columns the UI displays.

Postman query:

https://{{SNOW_INSTANCE}}.service-now.com/api/now/table/sys_dictionary?sysparm_fields=internal_type,sys_name,name,read_only,max_length,active,mandatory,comments,sys_created_by,element&name={{TableName}}&sysparm_display_value=all

I understand the reduced result set has something to do with them being real columns in the table vs. links to other tables but I can't find any documentation describing how to get the result set that the UI has using the REST api.

The follow on problem is that I can't find an example with an example payload where all standard fields have been filled out for the incident table so that I can populate as many fields as I have data for.

Answer 1

The reason you don't get all the columns back is because the table you are querying inherits from another table. You need to go through all the inheritance relationships first, finding all parent tables, then query the sys_dictionary for all of those tables.

In the case of the incident table, you need to query the sys_db_object table (table of all tables) to find the parent, which is the task table. Then query the sys_db_object table again to find its parent, which is empty, so we have all the relevant tables: incident and task. Obviously, you would want to write this code as a loop, building up a list of tables by querying the table at the end of the list.

Once you have this list, you can query sys_dictionary with the query: sysparm_query=name=incident^ORname=task, which should return your full list of columns.

Answer 2

I think you could do this by creating your own scripted rest api and iterating/inspecting the fields:

(function process(/*RESTAPIRequest*/ request, /*RESTAPIResponse*/ response) {

    var queryParams = request.queryParams; 
    var table = queryParams.table;
    var t = new GlideRecord(table);
    t.initialize();
    var fields = t.getElements(); //or getFields if global scope
    var fieldList = [];

    for (var i = 0; i < fields.length; i++) {
        var glideElement = fields[i]; //or field.get(i) if global scope
        var descriptor = glideElement.getED();

        var fldName = glideElement.getName().toString();
        var fldLabel = descriptor.getLabel().toString();
        var fldType = descriptor.getInternalType().toString();
        var canWrite = glideElement.canWrite();

        if (canWrite){
            fieldList.push({
                name: fldName,
                type: fldType,
                label: fldLabel,
                writable: canWrite
            });
        }       

    }

    return fieldList;


})(request, response);

This should save you the hassle of determining the inheritance of fields. Here's the sample output:

{
  "result": [
    {
      "name": "parent",
      "type": "reference",
      "label": "Parent",
      "writable": true
    },
    {
      "name": "made_sla",
      "type": "boolean",
      "label": "Made SLA",
      "writable": true
    },
    ...