Staging table list

rna · ‎04-06-2015

Is there any API which returns the list of staging tables (Both Default and Custom ones) ?

How can I differentiate between a normal custom table and a staging custom table?

edwin_munoz · ‎04-06-2015

Hello Rohit,

You can look for the tables that have sys_import_set_row as super class.

This is an example of a background script that you can run to get all the staging tables:

var stagingTables = new GlideRecord('sys_db_object');


stagingTables.addQuery('super_class.label', 'Import Set Row');


stagingTables.query();






while(stagingTables.next()){


         gs.print(stagingTables.name);


}

View solution in original post

edwin_munoz · ‎04-06-2015

Hi Rohit,

Sorry I'm not aware of the rotation tables, can you give me an example of a rotation table?

Thank you.

tony_barratt · ‎04-06-2015

Hi Edwin,

Your query give the correct result for me:

OOB

*** Script:imp_notification

*** Script: imp_user

*** Script: imp_location

*** Script: imp_computer

Not sure if Rohit is referring to to tables such as syslog which are rotated.

http://wiki.servicenow.com/index.php?title=Table_Rotation

But I only see the import set tables mentioned in the query you supplied.

Best Regards

Tony

rna · ‎04-06-2015

Hi Edwin / Tony,

The above query gives me the proper results for list of staging tables as expected, I miss quoted the question.

What I was referring to, was another API : https://<InstanceName>.service-now.com/api/now/v1/table/sys_db_object

This returns me the list of tables with the rotation tables also. Table Rotation - ServiceNow Wiki

I wanted to ignore this Rotation tables from the response of this API, for which could you please help me out.

tony_barratt · ‎04-06-2015

Hi Rohit,

Table API - ServiceNow Wiki

4 Methods

4.1 GET /api/now/v1/table/(tableName)

This method retrieves multiple records for the specified table with proper pagination information.

..

sysparm_query

An encoded query.

For example: (sysparm_query=active=true)(sysparm_query=caller_id=javascript:gs.getUserID()^active=true)

Note: The encoded query provides support for order by. To sort responses based on certain fields, use the ORDERBY and ORDERBYDESC clauses in sysparm_query. For example,sysparm_query=active=true^ORDERBYnumber^ORDERBYDESCcategory filters all active records and orders the results in ascending order by number first, and then in descending order by category.

You could define sysparm_query=nameNOT LIKE00

or something similar as the rotated tables have names ending in 00[0-9][0-9]

Best Regards

Tony

rna · ‎04-07-2015

Thanks Tony. It worked.