Report to view Record Count for all tables

To view your database footprint in GBs, you can either use Instance Observer or submit a “Database Footprint” request through the Now Support Portal (aka the HI portal). These reports will tell you the the size of the tables in GB. However, as of the time of this article, these reports do not tell you the record count for each table.

Unfortunately, there isn’t a quick/easy way to pull a record count record for all tables. So, I created a way to do this.

Create a table with 3 columns:

Table – Reference (sys_db_object)

Record Count – Integer

Last Counted – Date/Time

Create a schedule job to populate the table. You can set it to run for whatever frequency you plan to review the data (e.g. on demand, weekly, monthly, quarterly, annually).

NOTE: if you have a very large instance/footprint, this could take a while to run. Always test in sub-prod first and monitor for performance issues. Also, this does not take into account rotated tables. I might add in a check for that later.

var table = new GlideRecord('sys_db_object');
table.orderBy('name');
table.query();
while (table.next()) {

    var counter = aggregateCount(table.getValue('name'));

    var grLookup = new GlideRecord('x_214598_tablecoun_table_record_count');
    grLookup.addQuery('table', table.getUniqueValue());
    grLookup.query();
    if (grLookup.next()) {
        grLookup.record_count = counter;
		grLookup.last_counted = new GlideDateTime();
        grLookup.update();
    } else {

        var grInsert = new GlideRecord('x_214598_tablecoun_table_record_count');
        grInsert.initialize();
        grInsert.table = table.getUniqueValue();
        grInsert.record_count = counter;
		grInsert.last_counted = new GlideDateTime();
        grInsert.insert();
    }

}


function aggregateCount(table) {
    var gaCount = new GlideAggregate(table);
    gaCount.addAggregate('COUNT');
    gaCount.query();
    var count = 0;
    if (gaCount.next()) {
        count = gaCount.getAggregate('COUNT');
        return count;
    }
    return count;
}

Report to view Record Count for all tables

Share this: