Blog

Table Row Counts

With large ServiceNow instances, a good test is to periodically check table size. Reasons to check table size might include:

  • An import not configured with coalease properly, and is importing duplicate records.
  • Table Rotation not configured properly, resulting in large tables. This can impact performance.
  • Various other imports or interfaces that are creating large tables.

You can use different Database Analysis tools with the ServiceNow ODBC Driver to find out table row counts. However if you want to run a script to find table row counts or build a "Table Row Counts" application within ServiceNow, this article will show you how!

Scripts - Background Method

var grDictionary = new GlideRecord('sys_dictionary');
grDictionary.addQuery('internal_type', 'collection');
grDictionary.addQuery('name','!=','sys_template');
grDictionary.query();
gs.log('MLK Dictionary Query: ' + grDictionary.getEncodedQuery() + ' = ' + grDictionary.getRowCount());
while (grDictionary.next()) {
 var currentTable = grDictionary.name.toString();
 var count = new GlideAggregate(currentTable);
 count.addAggregate('COUNT');
 count.query();
 if (count.next()) {
gs.log('MLK Table Count: Table: '+currentTable+ ' contains '+ count.getAggregate('COUNT') + ' rows.');
 }
}

Table Row Counts Module Method

Create Table Row Counts Module

  1. Create New Table (Table Row Counts)
  1. Personalize Form
    Add New Field, "Table", String(50)
    Add New Field, "Row Count", Integer
  2. Personalize List
    Table, Row Count
  3. Create Script Includes
    Script Includes:
    Name: setTableRowCounts
    Client Callable: true
    Script:
    setTableRowCounts();
     
    function setTableRowCounts() {
    	//Clear Table Row Count Table
    	var grTableRowCounts = new GlideRecord('u_table_row_counts');
    	grTableRowCounts.query();
    	while (grTableRowCounts.next()) {
    		grTableRowCounts.deleteRecord();
    	}
    	gs.print('Deleted Table Row Counts Table');
    	//Set Table Row Counts
    	var grDictionary = new GlideRecord('sys_dictionary');
    	grDictionary.addQuery('internal_type', 'collection');
    	grDictionary.addQuery('name','!=','sys_template');
    	grDictionary.query();
    	gs.log('Dictionary Query: ' + grDictionary.getEncodedQuery() + ' = ' + grDictionary.getRowCount());
    	while (grDictionary.next()) {
    		var currentTable = grDictionary.name.toString();
    		var count = new GlideAggregate(currentTable);
    		count.addAggregate('COUNT');
    		count.query();
    		if (count.next()) {
    			var grTableRow = new GlideRecord('u_table_row_counts');
    			grTableRow.initialize();
    			grTableRow.u_table = currentTable;
    			grTableRow.u_row_count = count.getAggregate('COUNT');
    			grTableRow.insert();
    			gs.print('Table: '+grTableRow.u_table+ ' contains '+ grTableRow.u_row_count + ' rows.');
    		}
    	}
    }
  4. Adjust and create New Modules (Table Row Counts)
  5. Modules Created (Table Row Counts)
  6. List Control (Table Row Counts)
  7. UI Policy (Table Row Counts)

     

Try out the "Table Row Counts" Modules

1. Set "Set Table Row Counts"

7.png

2. View "Set Table Row Counts"