How to get table schema programatically via API or processor?

[andriyko]

Hello,

How can I get table's schema programatically (using REST).

Found some information here:

Data Dictionary Tables - ServiceNow Wiki

sys_db_object seems to return list of tables.

As fas as I understand sys_dictionary should return "additional details for each table and the definition for every column on each table. Each row represents either a column on a table or a table. ". So I would expect to have entries (tables and columns) that can be seen under "System Definition > Tables & Columns" section (table_columns.do).

But instead I get some weird list of entities, ~50 items, while X-Total-Count header shows > 15000 items.

So, how can I get data shown under "System Definition > Tables & Columns" via API? Particularly, I would like to be able to get schema of separate table via API.

Thanks in advance.

[edwin_munoz]

Hello Andriy,

You can query the sys_dictionary table by table name. Build a GET request with the following parameter:

sysparm_query=GOTOname%3DnameOfYourTable

you can create a custom view with the fields that you want, and use an additional parameter:

&sysparm_view%3DnameOfYourView

[andriyko]

Edwin, thanks for quick reply.

Just wanted to notice that query with GOTOname returns the same result as query name=NAME_OF_TABLE:

As for me it returns wrong (or at least weird) results.

Lets say, I want to get schema of 'incident' table - for each column get it's name and type.

GET /api/now/v1/table/sys_dictionary?sysparm_query=GOTOname%3Dincident HTTP/1.1" 200

returns list of 19 items

Each of them looks like:

{u'reference_qual': u'', u'reference_key': u'', u'internal_type': {u'link': u'https://babbar.com/api/now/v1/table/sys_glide_object/integer', u'value': u'integer'}, u'mandatory': u'false', u'dynamic_default_value': u'', u'calculation': u'', u'use_reference_qualifier': u'simple', u'choice': u'3', u'choice_field': u'', u'primary': u'false', u'dynamic_ref_qual': u'', u'sys_mod_count': u'0', u'sys_id': u'someID', u'defaultsort': u'', u'read_roles': u'', u'array': u'false', u'element_reference': u'false', u'read_only': u'false', u'column_label': u'Notify', u'create_roles': u'', u'reference': u'', u'comments': u'', u'xml_view': u'false', u'sys_class_name': u'sys_dictionary', u'sys_updated_by': u'system', u'reference_type': u'', u'virtual': u'false', u'spell_check': u'false', u'max_length': u'40', u'dependent_on_field': u'', u'next_element': u'', u'sys_created_by': u'system', u'dynamic_creation_script': u'', u'audit': u'false', u'use_dynamic_default': u'false', u'table_reference': u'false', u'use_dependent_field': u'false', u'dependent': u'', u'sys_updated_on': u'2015-01-01 17:32:06', u'reference_floats': u'false', u'text_index': u'false', u'attributes': u'', u'active': u'true', u'reference_qual_condition': u'', u'unique': u'false', u'sizeclass': u'', u'name': u'incident', u'reference_cascade_rule': u'', u'write_roles': u'', u'widget': u'', u'sys_created_on': u'2015-01-01 17:32:06', u'default_value': u'1', u'element': u'notify', u'foreign_database': u'', u'dynamic_creation': u'false', u'delete_roles': u'', u'choice_table': u'', u'display': u'false'}

So i take "value" from "internal_type" and "element", as the result i have:

>>> print as_tale(data)

| type                       | element                 |

+-----------------+-----------------+

| collection           |                                 |

| integer                 | business_stc       |

| integer                 | calendar_stc       |

| reference             | caller_id             |

| string                   | category               |

| reference             | caused_by             |

| integer                 | child_incidents |

| string                   | close_code           |

| integer                 | incident_state   |

| integer                 | notify                   |

| reference             | parent_incident |

| reference             | problem_id           |

| integer                 | reopen_count       |

| glide_date_time | resolved_at         |

| reference             | resolved_by         |

| reference             | rfc                         |

| integer                 | severity               |

| string                   | subcategory         |

| GUID                       | sys_id                   |

But a lot of columns are missing here compared to what we see in UI under "System Definition > Tables & Columns > Incident[incident]". For example "incident.work_notes" is not present.

Tables and Columns Module - ServiceNow Wiki

Seems like all fields marked with red square are not present, and i need to have fields that reside on the parent table in my results too. Is that possible?

[andriyko]

Ok. I have to query sys_db_object first to get super_class of my table, then get schema of parent table and my table. Is it possible to do this via processor (I mean to get schema of table)?

[edwin_munoz · Accepted solution]

Hello Andriy,

Yes, this can be done using a processor, you will just need to pass the table name parameter. please see code below:

var tables = [];



var tableName = g_request.getParameter("table-name");

var table = new GlideRecord("sys_db_object");

tables.push(tableName);


table.get("name", tableName);



var parentTable = table.super_class;



while(parentTable){

         tables.push(parentTable);

         parentTable = parentTable.super_class;

}



var fields= new GlideRecord("sys_dictionary");



for(var i = 0; i < tables.length; i++;){

         fields.addQuery("name", tables[i]);

}



fields.query();



g_processor.writeOutput("text/html", generateOutput(fields);



function generateOutput(fields){

         var outputStart = "<table>";

         var output = '';

         while(fields.next()){

                   output += "<tr>";

                   output += "<td>" + fields.name + "</td>";

                   output += "</tr>";

         }

         var outputEnd = "</table>";



         return outputStart + output + outputEnd ;

}

You just need to format the output as you need it. on the generate output function.

EDIT: If you only want to go up one level on the parent table remove the while loop since this is literally getting all the parent tables.

Good Luck!