Remove a Custom Table Data from Update Sets (unextend Application File (sys_metadata))?

[joshgrochowski]

When developing a few custom tables, I selected the "Track in Update Sets" option thinking this option had to be selected to track schema changes in the tables (add/remove/rename columns, etc). We pushed an update set to our testing instance today and I noticed the data from the custom tables was also transferred over in the Update Sets. Looking more into this, by selecing the "Track in Update Sets" option, the tables now extend Application File (sys_metadata).

Is there a way I can remove the "Track in Update Sets" from the tables? Or, in otherwords - unextend the custom tables from sys_metadata? If its not possible to break the connection to sys_metadata, is it possible to configure all update sets to ignore any changes to the custom tables?

Thanks for the assistance!

[Pradeep Sharma · Accepted solution]

Hi Josh,

Here is the script that you can run from background script to remove the value from "Extends table"

updateTableChanges();
function updateTableChanges()
{
   var gr = new GlideRecord('sys_db_object');
   gr.addQuery('sys_id','0799e3554f606a00bbc32d118110c77b'); //Replace sys_id with the exact table record
   gr.query();
   while(gr.next())
   {
   gr.super_class = '';
   gr.setWorkflow(false);
   gr.update();
   }
}

Please create a test table and then try this on dev first to make sure everything is working fine. Once you are done with the testing then you can try this with the table you have created.

Background Scripts — ServiceNow Elite

[joshgrochowski]

Thank you Pradeep! This appears to have worked as expected.

[Lane Roberts]

HI Josh,

Unfortunately, by running the change suggested by Pradeep, you will likely have broken your custom table. The parent table hosts the sys_updated_by, sys_created, and other "system" columns. Removing the parent reference severs the relationship those rows have with their data for those columns, and new rows no longer have storage or values for those columns. These actions also result in "orphaned" data in the parent table, too, and this will cause you other problems down the line.

If you've created new data in that table since running the change, then the damage is already done and you can't undo it by simply reconnecting the table with its parent, either, because only some of the rows will have matches in the parent table. And thus you'll run into odd errors with that table going forward.

You could attempt to fix this by backing up the existing data, followed by deleting the damaged table, and re-creating it and then importing the data back in again. We also recommend not updating sys_db_object or sys_dictionary using a custom script, without first vetting the potential consequences directly with ServiceNow Customer Support first.

Going forward, should you need to make such a change again, we would highly recommend using the following API instead of Pradeep's script:

Steps to reparent table:

1. Elevate your privileges to security admin
2. In scripts - background run this script (replacing variables as needed)
3. new GlideTableParentChange("custom_table").change("current_parent", "new_parent");

Important Notes:

• Always test first in a test or a development ServiceNow instance
• This API does work against tables with data in them, however it is best used against new or empty tables.
• This API should not be used against flattened tables that extend Task.
• This is a manual step and thus isn't captured in an update set.

So in your case you would want to try using 'new GlideTableParentChange("custom_table").change("sys_metadata", "");'

Cheers,

[Pradeep Sharma]

Thanks a lot Lane for the update.