How to create a copy of a table - Support and Troubleshooting

How to create a copy of a table Issue .ns-kb-css-body-editor-container { p { font-size: 12pt; font-family: Lato; color: #000000; } span { font-size: 12pt; font-family: Lato; color: #000000; } h2 { font-size: 24pt; font-family: Lato; color: black; } h3 { font-size: 18pt; font-family: Lato; color: black; } h4 { font-size: 14pt; font-family: Lato; color: black; } a { font-size: 12pt; font-family: Lato; color: #00718F; } a:hover { font-size: 12pt; color: #024F69; } a:target { font-size: 12pt; color: #032D42; } a:visited { font-size: 12pt; color: #00718f; } ul { font-size: 12pt; font-family: Lato; } li { font-size: 12pt; font-family: Lato; } img { display: block; max-width: ; width: auto; height: auto; } } 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_dictionary, 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.

CAVEAT: This script uses a deprecated script include used for Table Rotation to construct the new table and the resulting table can have differences such as adding the attribute edge_encryption_enabled on the new table's sys_dictionary records. That is it is not an exact one-to-one copy. The edge encryption attribute would only have impact if Edge Encryption had been licensed, setup and configured. Edge Encryption itself is deprecated and can no longer be purchased. See: Edge Encryption in the ServiceNow documentation.

Release .ns-kb-css-body-editor-container { p { font-size: 12pt; font-family: Lato; color: #000000; } span { font-size: 12pt; font-family: Lato; color: #000000; } h2 { font-size: 24pt; font-family: Lato; color: black; } h3 { font-size: 18pt; font-family: Lato; color: black; } h4 { font-size: 14pt; font-family: Lato; color: black; } a { font-size: 12pt; font-family: Lato; color: #00718F; } a:hover { font-size: 12pt; color: #024F69; } a:target { font-size: 12pt; color: #032D42; } a:visited { font-size: 12pt; color: #00718f; } ul { font-size: 12pt; font-family: Lato; } li { font-size: 12pt; font-family: Lato; } img { display: block; max-width: ; width: auto; height: auto; } } Any

Resolution .ns-kb-css-body-editor-container { p { font-size: 12pt; font-family: Lato; color: #000000; } span { font-size: 12pt; font-family: Lato; color: #000000; } h2 { font-size: 24pt; font-family: Lato; color: black; } h3 { font-size: 18pt; font-family: Lato; color: black; } h4 { font-size: 14pt; font-family: Lato; color: black; } a { font-size: 12pt; font-family: Lato; color: #00718F; } a:hover { font-size: 12pt; color: #024F69; } a:target { font-size: 12pt; color: #032D42; } a:visited { font-size: 12pt; color: #00718f; } ul { font-size: 12pt; font-family: Lato; } li { font-size: 12pt; font-family: Lato; } img { display: block; max-width: ; width: auto; height: auto; } } 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