Delete all records from all tables in an application

[Hendrik6]

Hello

Occasionally, I would like to delete all records in all tables of my application.

My current approach:

I am aware about the KB article Mass-Deletion and Excess Data Management Recommendations.

However it provides me only with an option to delete a single table. How can I get an array of all table names of the tables in my application? I would like to use the array to apply this method to each of them. Furthermore, I don't know where to store the script so that I can execute it manually whenever I need it. Where can I store a script so that I can manually execute it?

If my current approach is not the best practice please let me know.

Kind regards,

Hendrik

[Vamsi Sreenivas · Accepted solution]

Hi Hendrik,

Fix script would be the best place to store a script and run on demand. System Definition --> Fix Scripts from left navigation. You can query sys_db_object table where all the tables are registered, to get array of table names.

var grDB = new GlideRecord('sys_db_object');
grDB.addEncodedQuery('sys_scope=' + myApplicationSysId);
grDB.query();
while (grDB.next()) {
    var grTable = new GlideRecord(grDB.name);
    grTable.query();
    grTable.deleteMultiple();
}

Please mark my answer as HELPFUL / CORRECT if this help resolve your issue.

Regards,

Vamsi S

[Hendrik6]

Thank you Vamsi!

I still had to make some adjustments. However, then I could delete all data in my tables. I went to the application settings and created a query to find all tables of the application and lastly copied the query. 

Modified script:

grDB.addEncodedQuery(copied_query);

For anybody who wants to delete all data in the tables: I executed the script in a development instance. I would not run it on a production instance. As the other commentators pointed out, there are other options to remove data which are safer. 

[Sagar Pagar]

Hi,

What is the business requirement to delete the complete data from application? Can you explain.

Thank you,

Sagar Pagar

[shloke04]

Hi,

Deleting all records from all table in Application sounds scary to me. Would like to understand on what is the business use case here?

Have you thought about Data Archiving Rules setup in ServiceNow? Data archiving involves managing table size growth and archiving old data. It moves data that is no longer needed every day from primary tables to a set of archive tables.

Would recommend here to set up Data Archiving rule and not delete data. It's a straight forward configuration, you can find all details at link below:

https://docs.servicenow.com/en-US/bundle/sandiego-platform-administration/page/administer/database-rotation/concept/c_ArchiveData.html

https://docs.servicenow.com/bundle/sandiego-platform-administration/page/administer/database-rotation/task/t_CreateAnArchiveRule.html#t_CreateAnArchiveRule

If still you want to delete data, then there are  couple of option which you can use for deletion as listed below:

1. Fix Script

2. On Demand Scheduled Job.

Sample script for reference:

var tabletoDelete = ['incident', 'problem']; // Pass your Table Name here
for (var i = 0; i < tabletoDelete.length; i++) {
    deleteData(tabletoDelete[i]);
}

function deleteData(tableName) {
    var gr = new GlideRecord(tableName);
    gr.addEncodedQuery('active=true^caller_id=35b65717075b0110f3e0f2ae7c1ed0c4'); // Replace your query here
    gr.query();
    while (gr.next()) {
        gr.deleteRecord();
    }
}

Note: Please be mindful when you delete a Table data , child table data might also get deleted. So please try this in your lower prod instance.

Hope this helps. Please mark the answer as correct/helpful based on impact.

Regards,
Shloke