Rest API to fetch all table's columns for a given table

Jorge Martinez1
Kilo Contributor

‎03-31-2022 12:06 PM

Hello.

 

Do you know any way we can retrieve tables and columns metadata through the REST APIs? What I need is a list of columns for a given table.

The below query for the Incident table only returns the fields that are below to Incident, but Incident has a Task as a parent and I need to include those columns two also.

{instance}/api/now/v1/table/sys_dictionary?sysparm_query=name=incident

 

 

Thank you,

 

0 Helpfuls
  • 6,808 Views
5 REPLIES 5

Filipe Cruz
Kilo Sage
Kilo Sage

‎03-31-2022 02:49 PM

Hello Jorge,

You can try to fetch data for both task and incident table:

{instance}/api/now/v1/table/sys_dictionary?sysparm_query=nameINtask,incident


Please mark my answer as correct and helpful if it is relevant for you!

Thanks,

Filipe Cruz

Jorge Martinez1
Kilo Contributor
In response to Filipe Cruz

‎03-31-2022 03:06 PM

It gonna work but I'm looking for something that can be done programmatically without having to know what is hierarchy o a given table

0 Helpfuls

Aoife
Tera Guru

‎03-31-2022 02:50 PM

Check out this page: https://davidmac.pro/posts/2021-11-26-schema-meta-api/

The schema and meta APIs are quite useful for this purpose.

Aoife

Please mark as helpful and/or correct if this answer helps you.

0 Helpfuls

Hitoshi Ozawa
Giga Sage
Giga Sage

‎03-31-2022 04:21 PM

Hi Jorge,

To get all the columns in a table, create a Scripted API like below. I've defined a query parameter "name" to accept table name.

(function process( /*RESTAPIRequest*/ request, /*RESTAPIResponse*/ response) {
    try {
        var tableName = request.queryParams.name;
        if (tableName) {
            var grTable = new GlideRecord(tableName);
            grTable.setLimit(1);
            grTable.query();
            if (grTable.next()) {
                var grUtil = new GlideRecordUtil();
                var fieldList = grUtil.getFields(grTable);
                response.setBody(fieldList);
            } else {
                response.setError(new sn_ws_err.BadRequestError('There is no record in table' + tableName + '.'));
            }
        } else {
            response.setError(new sn_ws_err.BadRequestError('table ' + tableName + " does not exits."));
        }
    } catch (e) {
        response.setBody({
            "error": e.message
        });
    }
})(request, response);

Execution example:

https://<instance name>.service-now.com/api/522800/tablecolumns?name=incident

"sys_id""parent""caused_by""watch_list""upon_reject""sys_updated_on""origin_table""approval_history""number""state""sys_created_by""knowledge""order""cmdb_ci""delivery_plan""impact""contract""active""work_notes_list""priority""sys_domain_path""rejection_goto""business_duration""group_list""approval_set""wf_activity""universal_request""short_description""correlation_display""delivery_task""work_start""additional_assignee_list""notify""sys_class_name""service_offering""closed_by""follow_up""parent_incident""reopened_by""reassignment_count""assigned_to""variables""variables""variables""sla_due""comments_and_work_notes""escalation""upon_approval""correlation_id""made_sla""child_incidents""hold_reason""task_effective_number""resolved_by""sys_updated_by""opened_by""user_input""sys_created_on""sys_domain""route_reason""calendar_stc""closed_at""business_service""business_impact""rfc""time_worked""expected_start""opened_at""work_end""reopened_time""resolved_at""caller_id""subcategory""work_notes""close_code""assignment_group""business_stc""cause""description""origin_id""calendar_duration""close_notes""contact_type""incident_state""urgency""problem_id""company""activity_due""severity""comments""approval""due_date""sys_mod_count""reopen_count""sys_tags""location""category"

 

0 Helpfuls