Get Count of All PeopleSoft Tables with one SQL

While performing upgrades or applying maintenance to your PeopleSoft applications, you might want to get row count of all your PeopleSoft tables. There are few ways of doing it which we will discuss below and finally the one which I think is the easiest and most efficient way.

Option 1: Running UPGCOUNT AE

UPGCOUNT is a delivered Application Engine program that can be run via application designer or command line. This program will output a list with count of all PeopleSoft tables. You can read more about UPGCOUNT here.

Option 2: Get Count from DBA_TABLES (in ORACLE)

DBA_TABLES contains the row count of all the tables in a Oracle database. You can run below SQL to get the row count for PeopleSoft tables where Access ID or Owner ID is SYSADM. If PeopleSoft Owner ID is different in your case, please change the value of OWNER accordingly.

SELECT TABLE_NAME, NUM_ROWS FROM DBA_TABLES WHERE OWNER='SYSADM';

Above SQL will give you the row count of all tables in SYSADM schema. BUT this count may not always be right. Values in this table are populated based on whether statistics have been updated or not. Even if you perform update stats, still the count could be different than actual count. (try running count(*) on few tables and compare).

Option 3 (BEST OPTION): SQL using both DBA_TABLES and COUNT(*) to give you correct output.

We have found this one to be the best option. This SQL will get just the table names from DBA_TABLES and then run the count(*) on all the PeopleSoft tables. This is your best bet to get the actual row count.

select table_name, to_number(extractvalue(xmltype(dbms_xmlgen.getxml('select count(*) c from '||table_name)),'/ROWSET/ROW/C')) count from DBA_tables where owner='SYSADM';

Please let us know which option worked best for you or if you have any other efficient way to get the count of PeopleSoft tables.