Querying your MSSQL / MySQL / Oracle DB Server for Number of Rows

[intapiuser]

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';