Intro
ServiceNow allows Administrators to delete custom tables that are no longer needed. For example, delete a table from an application that is under development because the business requirements change.
A table is custom if an administrator created it and it is not part of a system upgrade or plugin activation. Custom table names always begin with u_ or x_ for scoped tables.
This operation is quite simple and fast.
The user can navigate under 'earch the table to delete.
After opening the table, the system shows two different UI Actions.
- Delete All Records
- Delete
As best practice, it is suggested to click 'Delete All Records' first and then 'Delete'.
Deleting all records before deleting the table ensures that the business logic is properly executed (for example, reference cascade rules or other delete business rules). If you do not delete all records from the table first, then you must manually fix any other records or tables that the table deletion impacts
When the user clicks the 'Delete' button a confirmation dialog box appears.
The system requires to enter the word 'delete' and click 'ok' to complete the operation.
The Exception: Baseline Scoped Applications.
The process described in the previous paragraph effects tables created by the users in 'Global' scope and custom scopes. However, this is not true for custom tables created in scopes included by baseline applications.
Real Life Example. HR Implementation.
The ServiceNow® HR Service Management application automates standard HR processes within your organisation. HR cases are created from HR requests or inquiries related to HR account or systems, benefits, payroll, performance management, reporting, talent acquisition, time and expense reporting, and more.
A specific set of pre-defined tables holds the details of HR cases.
The tables are identified with the term Center of Excellence (COE) and extend the HR Case [sn_hr_core_case] table. ServiceNow baseline provides 13 COEs to cover different areas of interest within the HR domain.
Here are some examples.
- HR Employee Relations Case [sn_hr_core_case_relations]
- HR Payroll Case [sn_hr_core_case_payroll]
- Talent Management [sn_hr_core_talent_management]
- HR Total Rewards Case [sn_hr_core_case_total_rewards]
- HR Workforce Administration Case [sn_hr_core_case_workforce_admin]
- HRIT Operations Case [sn_hr_core_case_operations]
A possible requirement of HR implementations can be the creation of a new dedicated COE to satisfy a specific HR Service delivered by a HR professional within an organisation.
In order to get the new COE, a new table is created extending HR Case [sn_hr_core_case].
The developer has 2 options:
- create the table as part of a new custom scope
- create the table in the existing Human Resources: Core [sn_hr_core]
If the second alternative is selected the new table inherits the official ServiceNow prefix instead of the usual x_.
The next image provides an example.
A custom table to cover HR Cases linked with the pandemic is created in the actual Human Resources: Core application. The system enforces the actual official prefix 'sn_hr_core'.
This is not the only relevant difference.
The system doesn't show the 'Delete' button usually available to remove custom tables.
Now think about the following scenarios:
- the new class has been created by mistake OR
- the new class has been created with the wrong name OR
- the requirement has changed, and the table is no longer required
It seems impossible to fix the issue.
How to delete a table created in baseline scoped application.
In order to remove the table the only option is to use a couple of line of code and rely on a specific API named Table Utils.
The API is probably one of the most powerful and dangerous publicly released by ServiceNow and must be used with EXTREME CAUTION.
The following 2 lines of code drops a database table permanently, deleting the table and all its data.
var tu = new TableUtils();
tu.dropAndClean("table_that_will_be_lost_forever");An Administrator can navigate under 'System Definition > Scripts - Background' and run the code.
The system will remove permanently the table.
Here's the message generated after the execution.
*** Script: removing table-specific ACL entries for sn_hr_core_case_covid
Altering storage table [sh$sys_security_acl_role]: ALTER TABLE sh$sys_security_acl_role 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 `dumrrqxc_source_primary`(`sys_id`)
*** Script: Begin ResourceSupport.buildTableResources(sh$sys_security_acl_role, undefined)
*** Script: End ResourceSupport.buildTableResources
*** Script: removing modules for sn_hr_core_case_covid
Expiring (StaleCategoryCacheFinder): []
*** Script: dropping table sn_hr_core_case_covid
Ensuring existence of target table before bulk copy: np$task
Altering storage table [np$task]: ALTER TABLE np$task ADD `np$context` VARCHAR(32) , ADD INDEX `ahdjxfes_source_primary`(`sys_id`)
*** Script: Begin ResourceSupport.buildTableResources(np$task, undefined)
*** Script: End ResourceSupport.buildTableResources
Copying data to target table [np$task] from [sn_hr_core_case_covid]...
DBOnlineAlterSupport: canCreateTrigger()=true
[TableChunkCopier] Starting [chunk_copy] operations
[TableChunkCopier] [0:00:00.006] Completed [chunk_copy] of task
Replication is not enabled on table: sn_hr_core_case_covid, not queueing replication drop table special db event
Time: 0:00:01.081 id: dev57283_1[glide.16] (connpid=15416) for: INSERT INTO sys_rollback_sequence (`context`, `target_class_name`, `storage_table_name`, `document_id`, `operation`,`txn_id`,`is_metadata`, `audit_delete`, `audit_delete_path`, `recovered`) SELECT '11486f61db32101052239444ca961941', 'sn_hr_core_case_covid', 'task', `sys_id`, 'delete', '464827e5db32101052239444ca9619c9', 0, NULL, NULL, 0 FROM task /* dev57283003, gs:B5A7A761DB32101052239444CA96194F, tx:5548a3a5db32101052239444ca961987 */
Altering storage table [sh$task]: ALTER TABLE sh$task 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 `bkfrmvrw_source_primary`(`sys_id`)
*** Script: Begin ResourceSupport.buildTableResources(sh$task, undefined)
*** Script: End ResourceSupport.buildTableResources
Truncating table: glide:sn_hr_core_case_covid
Time: 0:00:01.306 id: dev57283_1[glide.16] (connpid=15416) for: INSERT INTO sh$task (`parent`,`a_str_18`,`watch_list`,`upon_reject`,`sys_updated_on`,`approval_history`,`skills`,`number`,`a_str_14`,`a_str_13`,`a_str_12`,`state`,`a_str_11`,`a_str_10`,`sys_created_by`,`knowledge`,`order`,`cmdb_ci`,`delivery_plan`,`impact`,`contract`,`active`,`work_notes_list`,`priority`,`sys_domain_path`,`rejection_goto`,`business_duration`,`group_list`,`approval_set`,`wf_activity`,`short_description`,`correlation_display`,`delivery_task`,`work_start`,`additional_assignee_list`,`a_bln_1`,`a_bln_2`,`a_bln_3`,`a_bln_4`,`a_bln_5`,`a_bln_6`,`sys_class_name`,`service_offering`,`a_bln_7`,`sn_esign_esignature_c11n`,`closed_by`,`follow_up`,`reassignment_count`,`assigned_to`,`variables`,`sla_due`,`comments_and_work_notes`,`escalation`,`upon_approval`,`correlation_id`,`made_sla`,`sn_esign_document`,`sys_updated_by`,`opened_by`,`user_input`,`sys_created_on`,`sys_domain`,`closed_at`,`a_str_3`,`a_str_1`,`a_str_5`,`business_service`,`time_worked`,`expected_start`,`a_str_8`,`opened_at`,`work_end`,`work_notes`,`a_num_4`,`a_num_6`,`a_num_5`,`assignment_group`,`description`,`calendar_duration`,`close_notes`,`sys_id`,`contact_type`,`urgency`,`a_int_1`,`company`,`activity_due`,`a_ref_10`,`a_ref_8`,`a_ref_9`,`a_dtm_1`,`a_ref_12`,`a_ref_6`,`a_ref_7`,`comments`,`a_ref_11`,`a_ref_4`,`a_ref_14`,`a_ref_13`,`a_ref_5`,`a_ref_16`,`a_ref_2`,`approval`,`a_ref_3`,`a_ref_15`,`due_date`,`sys_mod_count`,`a_ref_1`,`a_ref_18`,`a_ref_17`,`location`, `sh$context`, `sh$operation`, `sh$sequence`, `sh$change_count`, `sh$first_recorded`, `sh$last_recorded`, `sh$first_txn_id`) SELECT `parent`,`a_str_18`,`watch_list`,`upon_reject`,`sys_updated_on`,`approval_history`,`skills`,`number`,`a_str_14`,`a_str_13`,`a_str_12`,`state`,`a_str_11`,`a_str_10`,`sys_created_by`,`knowledge`,`order`,`cmdb_ci`,`delivery_plan`,`impact`,`contract`,`active`,`work_notes_list`,`priority`,`sys_domain_path`,`rejection_goto`,`business_duration`,`group_list`,`approval_set`,`wf_activity`,`short_description`,`correlation_display`,`delivery_task`,`work_start`,`additional_assignee_list`,`a_bln_1`,`a_bln_2`,`a_bln_3`,`a_bln_4`,`a_bln_5`,`a_bln_6`,`sys_class_name`,`service_offering`,`a_bln_7`,`sn_esign_esignature_c11n`,`closed_by`,`follow_up`,`reassignment_count`,`assigned_to`,`variables`,`sla_due`,`comments_and_work_notes`,`escalation`,`upon_approval`,`correlation_id`,`made_sla`,`sn_esign_document`,`sys_updated_by`,`opened_by`,`user_input`,`sys_created_on`,`sys_domain`,`closed_at`,`a_str_3`,`a_str_1`,`a_str_5`,`business_service`,`time_worked`,`expected_start`,`a_str_8`,`opened_at`,`work_end`,`work_notes`,`a_num_4`,`a_num_6`,`a_num_5`,`assignment_group`,`description`,`calendar_duration`,`close_notes`,`sys_id`,`contact_type`,`urgency`,`a_int_1`,`company`,`activity_due`,`a_ref_10`,`a_ref_8`,`a_ref_9`,`a_dtm_1`,`a_ref_12`,`a_ref_6`,`a_ref_7`,`comments`,`a_ref_11`,`a_ref_4`,`a_ref_14`,`a_ref_13`,`a_ref_5`,`a_ref_16`,`a_ref_2`,`approval`,`a_ref_3`,`a_ref_15`,`due_date`,`sys_mod_count`,`a_ref_1`,`a_ref_18`,`a_ref_17`,`location`, '11486f61db32101052239444ca961941' `sh$context`, 'delete' `sh$operation`, '1741a80ce4e0000001' `sh$sequence`, 1 `sh$change_count`, NOW() `sh$first_recorded`, NOW() `sh$last_recorded`, '464827e5db32101052239444ca9619c9' `sh$first_txn_id` FROM task ON DUPLICATE KEY UPDATE `sh$change_count`=`sh$change_count`+1, `sh$last_recorded`=NOW(), `sh$operation`='delete', `sh$sequence`=VALUES(`sh$sequence`) /* dev57283003, gs:B5A7A761DB32101052239444CA96194F, tx:5548a3a5db32101052239444ca961987 */
[0:00:00.001] Truncate complete for: glide:sn_hr_core_case_covid
Altering storage table [sh$sys_storage_table_alias]: ALTER TABLE sh$sys_storage_table_alias 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 `sfcvjmmv_source_primary`(`sys_id`)
*** Script: Begin ResourceSupport.buildTableResources(sh$sys_storage_table_alias, undefined)
*** Script: End ResourceSupport.buildTableResources
Altering storage table [sh$sys_custom_db_object]: ALTER TABLE sh$sys_custom_db_object 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 `wrbvbyrh_source_primary`(`sys_id`)
*** Script: Begin ResourceSupport.buildTableResources(sh$sys_custom_db_object, undefined)
*** Script: End ResourceSupport.buildTableResources
Altering storage table [sh$ua_custom_table_inventory]: ALTER TABLE sh$ua_custom_table_inventory 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 `ohbfofxz_source_primary`(`sys_id`)
*** Script: Begin ResourceSupport.buildTableResources(sh$ua_custom_table_inventory, undefined)
*** Script: End ResourceSupport.buildTableResources
reloadLicenseCache: ua_license: 2020-08-23_01:48:04.980, # of unique apps associated with licenses: 0, time consumed (ms): 33
Time: 0:00:00.239 id: dev57283_1[glide.16] (connpid=15416) for: SELECT sys_dictionary0.`sys_id` FROM (sys_dictionary sys_dictionary0 INNER JOIN sys_metadata sys_metadata0 ON sys_dictionary0.`sys_id` = sys_metadata0.`sys_id` ) WHERE sys_dictionary0.`table_reference` = 1 /* dev57283003, gs:B5A7A761DB32101052239444CA96194F, tx:5548a3a5db32101052239444ca961987 */
Altering storage table [sh$sys_ui_moduleroles]: ALTER TABLE sh$sys_ui_moduleroles 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 `qatezqio_source_primary`(`sys_id`)
*** Script: Begin ResourceSupport.buildTableResources(sh$sys_ui_moduleroles, undefined)
*** Script: End ResourceSupport.buildTableResources
Altering storage table [sh$sys_ui_module]: ALTER TABLE sh$sys_ui_module 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 `eecqgzsr_source_primary`(`sys_id`)
*** Script: Begin ResourceSupport.buildTableResources(sh$sys_ui_module, undefined)
*** Script: End ResourceSupport.buildTableResources
TABLE DROP: admin dropped table sn_hr_core_case_covid
*** Script: removing gauges for sn_hr_core_case_covid
*** Script: removing forms for sn_hr_core_case_covid
*** Script: removing styles for sn_hr_core_case_covid
*** Script: removing forms sections for sn_hr_core_case_covid
*** Script: removing lists for sn_hr_core_case_covid
*** Script: removing related lists for sn_hr_core_case_covid
*** Script: removing references to sn_hr_core_case_covid
*** Script: removing dictionary entries for sn_hr_core_case_covid
Background message, type:info, message: Table deleted
This is the only way to fix the issue.
R0b0
.gif){kind=link}
