?
Solved

count all rows of all tables

Posted on 2007-10-05
4
Medium Priority
?
7,353 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
0
Comment
Question by:firepol
  • 2
  • 2
4 Comments
 
LVL 2

Expert Comment

by:zvmrws
ID: 20026769
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
 
LVL 2

Accepted Solution

by:
zvmrws earned 2000 total points
ID: 20026777
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
 
LVL 2

Author Comment

by:firepol
ID: 20032681
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
 
LVL 2

Author Comment

by:firepol
ID: 20032706
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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question