cancel
Showing results for 
Search instead for 
Did you mean: 
intapiuser
Community Team Member
Community Team Member

Introduction:

This post describes how to query your MSSQL, MySQL or Oracle DB to get the breakdown of the amount of rows in the DBs.

Prerequisites:

1. MSSQL / MySQL / Oracle DB
2. SQL Client Access to the DB (including Username and Password)

Procedure:

  1. Connect to your respective DB using the SQL client of your choice.
  2. Run the following script on the specific DB instance, according to the DB type.
Note: This should be done with the permission of the DB owner or administrator.
For MSSQL DBs :
CREATE TABLE #counts
(
table_name varchar(255),
row_count int
)
EXEC sp_MSForEachTable @command1='INSERT #counts (table_name, row_count) SELECT ''?'', COUNT(*) FROM ?'
SELECT table_name, row_count FROM #counts ORDER BY table_name, row_count DESC
SELECT SUM(row_count) AS total_row_count FROM #counts
For Oracle DBs:
set pages 999;
col count format 999,999,999;
spool countlist.txt
select
table_name,
to_number(
extractvalue(
xmltype(
dbms_xmlgen.getxml('select count(*) c from '||table_name))
,'/ROWSET/ROW/C')) count
from
user_tables
order by
table_name;
spool off;
For MySQL DBs:
SELECT TABLE_NAME, TABLE_ROWS FROM `information_schema`.`tables` WHERE `table_schema` = 'YOUR_DB_NAME';
Rate this article:
Version history
Last update:
‎03-02-2023 08:28 AM
Updated by:
Contributors