Querying your MSSQL / MySQL / Oracle DB Server for Number of Rows
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:
- Connect to your respective DB using the SQL client of your choice.
- 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
)
(
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 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
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;
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';
Updated 03-02-2023
