Issue
Sometimes you want to create a copy of a table for a test case, or you might need to back up data from a rotated shard before it gets truncated.
Although ServiceNow could create a backup of the table from the backend easily, you would not be able to access it from the instance, whether from a form or a list or from /sys.scripts.do. This is because the application would not be aware that the new table exists as it also requires associated records in the application metadata tables (sys_db_object, sys_dictioary, sys_documentation, etc.).
This article describes how to create a copy of an existing table so that you can access the copied table from the UI or /sys.scripts.do. Please note that the 'new' table will be empty and will not contain data. You have to copy data from the source table as a separate process.
CAVEAT: This script does not work for CMDB tables.
Resolution
Before New York release:
-
Navigate to /sys.scripts.do.
-
Copy the following code and customize it for your environment.
The first argument is the source table, the table to be copied. The second argument is the new/target table. The third argument is Boolean: set it to false if you don't want to or need to create the indexes. (The creation time will be fast either way as the table will be empty.)
cpTable('sys_upgrade_history_log', 'u_testcase', true);
function cpTable(strOldTable, strNewTable, bCopyIndexes) {
var tu = new TableUtils(strNewTable);
var bNewTableAlreadyExists = tu.tableExists();
if (bNewTableAlreadyExists) {
gs.print("WARNING: Target Table " + strNewTable + " already exists! Please choose a new target table name");
} else {
var gr = new GlideRecord(strOldTable);
gr.initialize();
var td = GlideTableDescriptor.get(strOldTable);
var tdNewTable = new TableDescriptor(strNewTable, gr.getLabel());
var dbo = new GlideRecord("sys_db_object");
dbo.addEncodedQuery("super_classISNOTEMPTY^name=" + strOldTable);
dbo.setLimit(1);
dbo.query();
if (dbo.next()) {
tdNewTable.setExtends(dbo.super_class + '');
}
tdNewTable.setFields(gr);
tdNewTable.copyAttributes(td);
tdNewTable.setRoles(td);
tdNewTable.create();
if (bCopyIndexes) {
tdNewTable.copyIndexes(strOldTable, strNewTable);
}
}
} -
Click Run Script.
The code will create a new table in the instance called u_testcase. You can now access this table via /u_testcase_list.do and from GlideRecord('u_testcase');.
The output might look like the following example.
[0:00:01.621] Script completed in scope global: script
Creating table: u_testcase
TableCreate for: u_testcase
DBTable.create() for: u_testcase
Replication is not enabled on table: u_testcase, not queueing replication table create special db event
*** Script: Begin ResourceSupport.buildTableResources(u_testcase, undefined)
*** Script: End ResourceSupport.buildTableResources
LicensingTableCreateListener: Initializing licensing attrs for table u_testcase
Time: 0:00:00.615 id: tundra_1[glide.2] for: SELECT sys_storage_alias0.`table_name`, sys_storage_alias0.`element_name`, sys_storage_alias0.`storage_alias` FROM sys_storage_alias sys_storage_alias0 WHERE sys_storage_alias0.`storage_alias` != sys_storage_alias0.`element_name` /* tundra004, gs:329947A4DBAE4700671C51035E9619B8, tx:52c94328dbae4700671c51035e9619cb */
Creating index(es): ALTER TABLE `u_testcase`ADD INDEX (`sys_source_table`) ,ADD INDEX (`update_set`) ,ADD INDEX (`upgrade_history`)
Duplicate index, skipping: u_testcase([sys_source_table]) NONUNIQUE
Duplicate index, skipping: u_testcase([update_set]) NONUNIQUE
Redundant index check on u_testcase found redundant index upgrade_history (upgrade_history) NONUNIQUE; caused by new index (upgrade_history,file_name,sys_recorded_at) NONUNIQUE
Redundant index check on u_testcase found 1 redundant indexes
Creating index(es): ALTER TABLE `u_testcase`ADD INDEX (`upgrade_history`, `file_name`, `sys_recorded_at`) ,ADD INDEX (`upgrade_history`, `order`) ,ADD INDEX (`upgrade_history`, `disposition`, `resolution_status`, `changed`, `order`) ,ADD INDEX (`upgrade_history`, `resolution_status`, `disposition`, `type_priority`) ,ADD INDEX (`upgrade_history`, `disposition`, `changed`) ,ADD INDEX (`order`)
Dropping index(es): ALTER TABLE `u_testcase` DROP INDEX `upgrade_history`If you run the same code again, an error occurs:
[0:00:00.000] Script completed in scope global: script
*** Script: WARNING: Target Table u_testcase already exists! Please choose a new target table name
New York and subsequent releases:
-
Navigate to /sys.scripts.do.
-
Copy the following code and customize it for your environment.
The first argument is the source table, the table to be copied. The second argument is the new/target table. The third argument is Boolean: set it to false if you don't want to or need to create the indexes. (The creation time will be fast either way as the table will be empty.)
cpTable('sys_upgrade_history_log', 'u_testcase', true);
function cpTable(strOldTable, strNewTable, bCopyIndexes) {
var tu = new TableUtils(strNewTable);
var bNewTableAlreadyExists = tu.tableExists();
if (bNewTableAlreadyExists) {
gs.print("WARNING: Target Table " + strNewTable + " already exists! Please choose a new target table name");
} else {
var gr = new GlideRecord(strOldTable);
gr.initialize();
var td = GlideTableDescriptor.get(strOldTable);
var tdNewTable = new SNC.TableRotationBootstrap(strNewTable, gr.getLabel());
var dbo = new GlideRecord("sys_db_object");
dbo.addEncodedQuery("super_classISNOTEMPTY^name=" + strOldTable);
dbo.setLimit(1);
dbo.query();
if (dbo.next()) {
tdNewTable.setExtends(dbo.super_class.name + '');
}
tdNewTable.setFields(gr);
tdNewTable.copyAttributes(td);
tdNewTable.create();
if (bCopyIndexes) {
tdNewTable.copyIndexes(strOldTable, strNewTable);
}
}
} -
Click Run Script.
The code will create a new table in the instance called u_testcase. You can now access this table via /u_testcase_list.do and from GlideRecord('u_testcase');.
The output might look like the following example.
Creating table: u_testcase
TableCreate for: u_testcase
DBTable.create() for: u_testcase
Replication is not enabled on table: u_testcase, not queueing replication table create special db event
*** Script: Begin ResourceSupport.buildTableResources(u_testcase, undefined)
*** Script: End ResourceSupport.buildTableResources
LicensingTableCreateListener: Initializing licensing attrs for table u_testcase
Creating index(es): ALTER TABLE `u_testcase`ADD INDEX (`sys_source_table`) ,ADD INDEX (`upgrade_history`) ,ADD INDEX (`update_set`)
[0:00:13.170] DBTable.create of: u_testcase
[0:00:14.839] Table create for: u_testcase
Duplicate index, skipping: u_testcase([sys_source_table]) NONUNIQUE
Duplicate index, skipping: u_testcase([update_set]) NONUNIQUE
Redundant index check on u_testcase found redundant index upgrade_history (upgrade_history) NONUNIQUE; caused by new index (upgrade_history,order) NONUNIQUE
Redundant index check on u_testcase found 1 redundant indexes
Creating index(es): ALTER TABLE `u_testcase`ADD INDEX (`upgrade_history`, `order`) ,ADD INDEX (`upgrade_history`, `resolution_status`, `disposition`, `type_priority`) ,ADD INDEX (`upgrade_history`, `file_name`, `sys_recorded_at`) ,ADD INDEX (`upgrade_history`, `disposition`, `changed`) ,ADD INDEX (`upgrade_history`, `disposition`, `resolution_status`, `changed`, `order`) ,ADD INDEX (`order`)
Dropping index(es): ALTER TABLE `u_testcase` DROP INDEX `upgrade_history`
Altering storage table [sh$sys_cache_flush]: ALTER TABLE sh$sys_cache_flush ADD `sh$context` VARCHAR(32) , ADD `sh$operation` VARCHAR(40) , ADD `sh$change_count` INTEGER , ADD `sh$first_recorded` DATETIME , ADD `sh$last_recorded` DATETIME , ADD `sh$sequence` VARCHAR(40) , ADD `sh$first_txn_id` VARCHAR(32) , ADD INDEX `mnixgjyj_source_primary`(`sys_id`)
*** Script: Begin ResourceSupport.buildTableResources(sh$sys_cache_flush, undefined)
*** Script: End ResourceSupport.buildTableResources