How to get table schema programatically via API or processor?

andriyko · ‎03-02-2015

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 · ‎03-02-2015

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!

View solution in original post

andriyko · ‎03-03-2015

Thank you, Edwin. That works fine. This is what I have now, trying to return JSON.

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


var table = new GlideRecord("sys_db_object");


var tables = [];


table.get("name", tableName);


var parentTable = table.super_class;


while(parentTable){


   var gr = new GlideRecord("sys_db_object");


   gr.get(parentTable);


   tables.push(gr.name);


       parentTable = parentTable.super_class;


}


var dictionaryEntries = new GlideRecord("sys_dictionary");


var grOR = dictionaryEntries.addQuery("name", tableName);


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


       grOR.addOrCondition("name", tables[i]);


}


dictionaryEntries.query();


var qq = dictionaryEntries.getEncodedQuery();


var rows = dictionaryEntries.getRowCount();










function generateOutput(fields){


   var items = [];


       while(fields.next()){


   var name = fields.name;


               var elem = fields.element;


   var tt = fields.internal_type;


   var item = {"name": name, "element": elem, "ftype": tt};


               items.push(item);


   }


   return items;


}


var results = generateOutput(dictionaryEntries);


var json = new JSON();


g_processor.writeOutput("application/json", json.encode({"results": results}));

When I print that data it has correct values, but when return JSON:

>>> resp.text

u'{"results":[{"element":{},"ftype":{},"name":{}},{"element":{},"ftype":{},"name":{}},{"element":{},"ftype":{},"name":{}},{"element":{},"ftype":{},"name":{}},{"element":{},"ftype":{},"name":{}},{"element":{},"ftype":{},"name":{}},{"element":{},"ftype":{},"name":{}},{"element":{},"ftype":{},"name":{}},{"element":{},"ftype":{},"name":{}},{"element":{},"ftype":{},"name":{}},{"element":{},"ftype":{},"name":{}},{"element":{},"ftype":{},"name":{}},{"element":{},"ftype":{},"name":{}},{"element":{},"ftype":{},"name":{}},{"element":{},"ftype":{},"name":{}},{"element":{},"ftype":{},"name":{}},{"element":{},"ftype":{},"name":{}},{"element":{},"ftype":{},"name":{}},{"element":{},"ftype":{},"name":{}},{"element":{},"ftype":{},"name":{}},{"element":{},"ftype":{},"name":{}},{"element":{},"ftype":{},"name":{}},{"element":{},"ftype":{},"name":{}},{"element":{},"ftype":{},"name":{}},{"element":{},"ftype":{},"name":{}},{"element":{},"ftype":{},"name":{}},{"element":{},"ftype":{},"name":{}},{"element":{},"ftype":{},"name":{}},{"element":{},"ftype":{},"name":{}},{"element":{},"ftype":{},"name":{}},{"element":{},"ftype":{},"name":{}},{"element":{},"ftype":{},"name":{}},{"element":{},"ftype":{},"name":{}},{"element":{},"ftype":{},"name":{}},{"element":{},"ftype":{},"name":{}},{"element":{},"ftype":{},"name":{}},{"element":{},"ftype":{},"name":{}},{"element":{},"ftype":{},"name":{}},{"element":{},"ftype":{},"name":{}},{"element":{},"ftype":{},"name":{}},{"element":{},"ftype":{},"name":{}},{"element":{},"ftype":{},"name":{}},{"element":{},"ftype":{},"name":{}},{"element":{},"ftype":{},"name":{}},{"element":{},"ftype":{},"name":{}},{"element":{},"ftype":{},"name":{}},{"element":{},"ftype":{},"name":{}},{"element":{},"ftype":{},"name":{}},{"element":{},"ftype":{},"name":{}},{"element":{},"ftype":{},"name":{}},{"element":{},"ftype":{},"name":{}},{"element":{},"ftype":{},"name":{}},{"element":{},"ftype":{},"name":{}},{"element":{},"ftype":{},"name":{}},{"element":{},"ftype":{},"name":{}},{"element":{},"ftype":{},"name":{}},{"element":{},"ftype":{},"name":{}},{"element":{},"ftype":{},"name":{}},{"element":{},"ftype":{},"name":{}},{"element":{},"ftype":{},"name":{}},{"element":{},"ftype":{},"name":{}},{"element":{},"ftype":{},"name":{}},{"element":{},"ftype":{},"name":{}},{"element":{},"ftype":{},"name":{}},{"element":{},"ftype":{},"name":{}},{"element":{},"ftype":{},"name":{}},{"element":{},"ftype":{},"name":{}},{"element":{},"ftype":{},"name":{}},{"element":{},"ftype":{},"name":{}},{"element":{},"ftype":{},"name":{}},{"element":{},"ftype":{},"name":{}},{"element":{},"ftype":{},"name":{}},{"element":{},"ftype":{},"name":{}},{"element":{},"ftype":{},"name":{}},{"element":{},"ftype":{},"name":{}},{"element":{},"ftype":{},"name":{}},{"element":{},"ftype":{},"name":{}},{"element":{},"ftype":{},"name":{}},{"element":{},"ftype":{},"name":{}},{"element":{},"ftype":{},"name":{}},{"element":{},"ftype":{},"name":{}},{"element":{},"ftype":{},"name":{}}]}'

Tim Owen · ‎08-31-2015

I am trying to pull all column names for a given table with this script. It works on any single level table, but does not work on extended tables. I can pull Task easily as well as any tables I created that are NOT extended. I need to pull all columns for the given table plus any columns from the table it is extended from. Can you give me any clues on this?

Thank you.

andriyko · ‎08-31-2015

Hi Tim,

Please see this

Scoped GlideTableHierarchy API Reference - ServiceNow Wiki

For example:

var tables = new GlideTableHierarchy('incident').getTables();

['task', ...]

var extensions = new GlideTableHierarchy('incident').getTableExtensions();

To get fields:

var rec = new GlideRecord('sys_dictionary')

rec.addQuery('name', 'incident');

rec.addQuery('internal_type', 'string');

...

Also there is a great blog

http://www.cavucode.com/blog/2015/3/15/fujiforty-table-hierarchy-api

postwick · ‎03-02-2015

Check out http://erm4sn.com