count all rows of all tables

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
LVL 2
firepolAsked:
Who is Participating?
 
zvmrwsConnect With a Mentor Commented:
The above just does the tables for your schema.

For all the tables in the database:

PROC SQL;
  SELECT TABLE_NAME,NUM_ROWS FROM ALL_TABLES
  QUIT;
0
 
zvmrwsCommented:
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
0
 
firepolAuthor 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).
0
 
firepolAuthor 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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.