Can I create a custom table via a script?

Todd O · ‎12-20-2016

Is it possible to create a SN custom table via a script? I would like to create many tables that are coming from a different database. I would like to write a script to read in that ddl and then translate that to whatever SN needs to create these tables. It will save me a lot of typing. Thank you in advance.

Todd

drjohnchun · ‎12-21-2016

Here's an example of adding a new extended table and a column

// create a new table 'abc' by extending 'task' and add new string column 'myfield'

var table_name = 'abc', extends_table = 'task', fname = 'myfield';


var attrs = new Packages.java.util.HashMap();
var ca = new GlideColumnAttributes(fname);
ca.setType("string");
ca.setUsePrefix(false);
attrs.put(fname, ca);


var tc = new GlideTableCreator(table_name , table_name);
tc.setColumnAttributes(attrs);
if(typeof extends_table != 'undefined') tc.setExtends(extends_table);
tc.update();

You can run this in Background Script and go to System Definition > Tables or Tables & Columns to see the new table and column.

View solution in original post

drjohnchun · ‎05-29-2017

Hi Mattia - "is_extendable" is a field in the sys_db_object table that determines if a table is extensible and it's not part of the GlideRecord class. Once you know this from working with tables, you can set the field value using the GlideRecord class.

You can navigate to System Definition > Tables, then open a table form. In the Controls tab, you see the Extensible checkbox. To see the field name, right-click on the label "Extensible" and you see what the field name is.

drjohnchun · ‎12-21-2016

To clarify, the two parameters for GlideTableCreator() are

GlideTableCreator(table_name , table_label)

The created table name is exactly as you defined using the first parameter; it doesn't prefix it with "u_" or application scope (the same with column names created using attrs.put(), which may be useful when you're trying to replicate the schema of a remote table, as you intend to do).

Todd O · ‎12-21-2016

John,

Your help has be tremendous, thank you! I am very very interested in understanding additional capabilities of the GlideColumnAttributes and GlideTableCreator classes (and perhaps other associated classes). I want to extend a bit of what you showed me already. For example, additional attributes for a column like size, reference (and table to reference), label, etc.

How did you come to understand some of this? Is there any source I can use to uncover these gold nuggets? E.g., the setType, setUsePrefix, etc are not exposed in documentation any where.

Thank you!!!!

Todd

benlittle · ‎09-30-2020

For the fields, it does not add the u_ prefix is because of the ca.setUsePrefix(false); line. If this is true, it will add the u_. If this is false, it will not.

Note that I have found no way to stop the system from making the field name all lowercase.

drjohnchun · ‎12-21-2016

When you're done with testing, you can use TableUtils - ServiceNow Wiki to drop the test tables.