Generate Schema Definition

Updated the code because of a bug
At one of my clients, we were integrating with a number of systems. One in particular had asked for a schema definition of the tables they were reading from and writing to.

So I decided to write a script to provide a JSON output of the schema. As it’s going through the columns of the starting table, if it finds a reference field, it’ll do the columns of that table, and if it finds a reference field there it will do the same again.

It does this up to 5 levels deep. If it finds a self referencing reference field (such as manager on the sys_user table, it’ll just put in ‘Recursive Reference’).

ServiceNow will output it in one long string, but if you go to a site like Dirty Markup and paste the details, it’ll format it nicely for you.

generateSchema('incident');
function generateSchema(table) {
var schema = _getTableData(table, 0);
gs.print(new JSON().encode(schema));
}
function _getTableData(table, level) {
level++;
if (level > 5) {
return {};
}
var tableSchema = {};
tableSchema.Atable = table;
var tab = new GlideRecord(table);
tableSchema.Alabel = tab.getClassDisplayValue();
tableSchema.columns = [];
var gr = new GlideRecord('sys_dictionary');
gr.addQuery('name', table);
gr.addNotNullQuery('element');
gr.query();
while (gr.next()) {
var fieldObj = {};
fieldObj.Acolumn = gr.element + '';
fieldObj.Alabel = gr.column_label + '';
fieldObj.Atype = gr.internal_type.getDisplayValue();
if (fieldObj.Atype == 'Reference') {
var ref = gr.reference + '';
if (ref == table) {
fieldObj.Areference = 'Recursive reference';
}
else {
fieldObj.reference = _getTableData(gr.reference + '', level);
}
}
tableSchema.columns.push(fieldObj);
}
return tableSchema;
}

2 Comments

  1. This runs fine for the base table once I replaced the curved quotes with a straight single-quote but doesn’t seem to walk through the fields on the referenced tables. What do I replace ” with in my text editor? (Tried two straight single quotes and toString() without luck so far . . . have run it as a fix and background script with the same result.) Or am I missing something else about how/where to run?

    Like

    • Running it as a background script is the right thing to do.
      I hadn’t noticed wordpress changed all the apostrophes so it wouldn’t work anymore. A bit annoying.

      Also, there’s bug in here which is why it wasn’t going into deeper levels.

      This line if (fieldObj.type == ‘Reference’) { should actually say if (fieldObj.Atype == ‘Reference’) {

      I’ll update the article. Thanks for pointing it out

      Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s