count all records in all tables in oracle db

[samuel_brierley]

Hi,

i have an oracle database with lots of tables and views but not all have data in them.

is there a simple way of looping through the tables and creating a new table with table name as one dimension and records in another.

[adamdavi3s · Accepted solution]

I don't know oracle very well but you could do something like this I think, I've written it on the fly so you might need to tweak it a bit & add the code for views

In later versions of Oracle, google suggests that this will work:

SQL select table_name, column_value cnt
  from user_tables, xmltable (('count(ora:view("'||table_name||'"))'))

Otherwise something like:

//load list of tables

tables:

load *;

SQL SELECT table_name

FROM dba_tables;

LET CKNumRows=NoOfRows('tables');

FOR p=0 to $(CKNumRows) -1 // start a loop, peek is zero based so if only one row need to do 0 to 0 hence the -1

LET vTable=Peek('table_name',p,'tables'); //get the next table namefrom our table

//load the fact file

fact:

LOAD '$(vTable)' as filename, count;

SQL SELECT count(*)

FROM '$(vTable)';

Please remember to mark this as helpful or the correct answer if I have helped you or answered your question.