Can I create a custom table via a script?

[Todd O]

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 · Accepted solution]

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.

[Todd O]

That was the solution John. It worked perfectly. I did get one odd message from the output though. See below and the bolded line. Why would it say this when our script is NOT trying to create the "task" table but rather just extending from it?   We aren't trying to "alter" it at all, we are merely trying to create a new table. Thanks.

Todd

Creating table: abc

TableCreate for: abc

DBTable.create() for: abc

Table task already exists and is table per hierarchy, performing table alter(s) instead of create

Replication is not enabled on table: abc, not queueing replication table create special db event

LicensingTableCreateListener: Setting licensing attrs for table abc

[drjohnchun]

Yes, it sounds rather confusing and as if you've done something to the task table. I'd simply read it as the new table is extended from task. When creating a new table that's not extended, you won't see this message, of course.

For your use cases, you might appreciate the fact that this solution doesn't prefix column names with "u_".

[rajeshkareti]

Hello,

When I try to run this from scoped application, system is throwing the error message saying that "GlideTableCreator is not allowed from scoped application". Can you suggest the best practice to run this from scoped application?

[Todd O]

I was never able to get it to run in a scoped application. I went ahead in Global instead.