We help IT Professionals succeed at work.

count all rows of all tables

firepol
firepol asked
on
7,392 Views
Last Modified: 2013-11-16
Hi, I'm a new SAS user.

I need to create a report where I see, for all tables of an oracle database, their count(*).

I've begun to do it manually, here a sample of the program I created:

libname TESTDB oracle user='user' pass='password' path='testdb.prd' schema='TEST';
run;

proc sql;                                                                                                              
connect to oracle as mycon(user=user                                                                              
        password=password path='testdb.prd');                                                                          

/* Count for table1 */
create table Count_table1 as select *                                                                                          
  from connection to mycon                                                                                            
(select count(*)
from TEST.table1
);

/* Count for table2 */
create table Count_table2 as select *                                                                                          
  from connection to mycon                                                                                            
(select count(*)
from TEST.table2
);

quit;
run;

Now, I obtain the count for table1 and table2 under Libraries > Work > Count_table1 and Count_Table2

Here my requests:

1) I'd like to automatize this process. Is there a way or programming way to get all tables names and for each one of them take their count(*)? If yes, how?

2) If for 1) the answer is "yes", is it possible, instead of creating a table in "Work", to print the output of all tables count in a single sheet/report? How?

Thank you
Comment
Watch Question

Commented:
Sorry, I am a bit rusty with SAS and don't have a current license to test this, but...

#1:  Check out http://www.ss64.com/orad/USER_TABLES.html

Try to do this:
PROC SQL;
  SELECT TABLE_NAME,NUM_ROWS FROM databaseid.USER_TABLES
  QUIT;
I
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Thank you zvmrws.

The query with "ALL_TABLES" at the end works. The one with "databaseid.USER_TABLES" (I replaced the databaseid) doesn't... maybe a problem of permissions?

The strange thing is that the NUM_ROWS gives a different result than doing COUNT(*) for each table.

E.g. with the query "SELECT TABLE_NAME,NUM_ROWS FROM ALL_TABLES"
NUM_ROWS of table1 is: 402917

With the query of my example "select count(*) from table1"
count(*) is 403936

This way the query you give me is not precise to describe exactly the tables if it misses thousands of rows... (the problem occurs for almost all big tables).

Author

Commented:
A colleague explained me that ALL_TABLES is statistical, thus it's not aligned with the real data. Anyway, not bad, every night it's updated...

The query with databaseid.USER_TABLES works if I remove "databaseid.". But gives me in the log " No rows were selected".

Well, you answered my question, thus I thank you ;-)

New questions will for sure come.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.