How to find large tables

[Daichi Ishikawa]

What's an easy way to identify large tables (by number of rows and columns) in a database?

Background:

According to the following documentation, using database views that include large tables negatively impacts performance. As a system administrator, I'd like to know if there's a simple way to find these large tables.

Thank you!

---

The accumulated impact on performance grows as the number of tables that are included in the view and the number of records that those tables contain increases. To maximize the performance of the database view, ensure that the ‘where’ clauses that are defined in the database view are based on indexed fields.

---

[Docs] Creating database views for reporting

https://www.servicenow.com/docs/bundle/washingtondc-platform-administration/page/use/reporting/conce...

[Ravi Chandra_K]

Hello @Daichi Ishikawa 

There's one trick for this.

In the filter navigator, search for Remove Pagination Count Helper, which will give list of Tables with highest no of rows/records 

Please mark the answer as helpful and correct if helped.

Kind Regards,

Ravi Chandra 

[Juhi Poddar]

Hello @Daichi Ishikawa 

Run this code in background script to know all the table with their row and column count.

var tables = new GlideRecord('sys_db_object');
tables.addQuery('super_class', ''); // Top-level tables only
tables.addNotNullQuery('sys_update_name'); // Exclude system tables
tables.query();

while (tables.next()) {
    var tableName = tables.getValue('name');
    
    // Get record count
    var recordCount = 0;
    try {
        var ga = new GlideAggregate(tableName);
        ga.addAggregate('COUNT');
        ga.query();
        if (ga.next()) {
            recordCount = ga.getAggregate('COUNT');
        }
    } catch (e) {
        gs.warn('Unable to retrieve record count for table: ' + tableName);
    }

    // Get column count
    var columnCount = 0;
    var dictionary = new GlideRecord('sys_dictionary');
    dictionary.addQuery('name', tableName);
    dictionary.query();
    while (dictionary.next()) {
        columnCount++;
    }

    gs.info('Table: ' + tableName + ' | Rows: ' + recordCount + ' | Columns: ' + columnCount);
}

Hope this helps!

"If you found my answer helpful, please give like and mark it as an "accepted solution". It helps others find the solution more easily and supports the community!"

Thank You
Juhi Poddar