Home » SQL & PL/SQL » SQL & PL/SQL » Script for Table names and row counts
Script for Table names and row counts [message #324971] Wed, 04 June 2008 12:00 Go to next message
Duane
Messages: 452
Registered: December 2002
Senior Member
Anyone have a script that lists out Table names and row counts?

Thanks.
Re: Script for Table names and row counts [message #324972 is a reply to message #324971] Wed, 04 June 2008 12:02 Go to previous messageGo to next message
BlackSwan
Messages: 25045
Registered: January 2009
Location: SoCal
Senior Member
SELECT TABLE_NAME, NUM_ROWS FROM USER_TABLES;
Re: Script for Table names and row counts [message #324976 is a reply to message #324971] Wed, 04 June 2008 12:09 Go to previous messageGo to next message
coleing
Messages: 213
Registered: February 2008
Senior Member
after analyzing the tables, and its only an estimate (usually pretty accurate though).
Re: Script for Table names and row counts [message #324977 is a reply to message #324971] Wed, 04 June 2008 12:12 Go to previous messageGo to next message
BlackSwan
Messages: 25045
Registered: January 2009
Location: SoCal
Senior Member
>after analyzing the tables,
Tables (&indexes) should ALWAYS be "recent" analyzed.
>and its only an estimate
Not necessarily.
> (usually pretty accurate though)
Typically close enough even on an OLTP system.
Re: Script for Table names and row counts [message #324979 is a reply to message #324971] Wed, 04 June 2008 12:20 Go to previous messageGo to next message
coleing
Messages: 213
Registered: February 2008
Senior Member
depends what you are using it for.

estimate of size of tables, no problem.

as a means of controlling whats in each table by the exact row, dont do it.

You could use something like this (not tested as dont have a session - but u should get the general idea from it);

select 'select ''' || table_name || ''', count(1) from ' || table_name || ' union all '
from user_tables;


Then save the rows into a text file, and run the statement that it produces (without the final union all).
Re: Script for Table names and row counts [message #324980 is a reply to message #324971] Wed, 04 June 2008 12:24 Go to previous messageGo to next message
Duane
Messages: 452
Registered: December 2002
Senior Member
I believe they want an actual count but I could be wrong. They want to do a comparison between the tables from one day to the next day.
Re: Script for Table names and row counts [message #324986 is a reply to message #324979] Wed, 04 June 2008 12:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Stop this stupid "count(1)", you want to count rows not 1.

Regards
Michel
Re: Script for Table names and row counts [message #325031 is a reply to message #324971] Wed, 04 June 2008 18:13 Go to previous messageGo to next message
coleing
Messages: 213
Registered: February 2008
Senior Member
I know, I know, but its a habit
I dont think there is any harm in it. (apart from some weird bug I saw someone report on here over using a db link) Ive never seen that happen though.

Now before anyone flames up, I know count(*) is oracle syntax for "count all rows". I also know that count(1) works as well. Im just saying its down to coding style, and unless Im proven it doesnt work, Im staying with count(1) as its a habit.

Anyway, here is my attempt at making a case for count(1) over count(*). Hear me out first....

If I Issue:-
select 1 from table1;

I will get a virtual column with "1" in it for each row in the table.

Extending that, if I use count(1), This would mean "count my virtual column value of 1, for all rows in the table".

now, look at the below example:-

SQL>
SQL> create table dan(field1 varchar2(10), field2 varchar2(10));
Table created
SQL> insert into dan values (null,null);
1 row inserted
SQL> insert into dan values (null,1);
1 row inserted
SQL> insert into dan values (1,null);
1 row inserted
SQL> insert into dan values (1,null);
1 row inserted
SQL> insert into dan values (1,1);
1 row inserted
SQL> commit;
Commit complete

SQL> select nvl(field1,'null'), nvl(field2,'null')
  2  from dan;
NVL(FIELD1,'NULL') NVL(FIELD2,'NULL')

------------------ ------------------

null               null

null               1

1                  null

1                  null

1                  1

SQL> select count(1) from dan;
  COUNT(1)
----------
         5

SQL> select count(*) from dan;
  COUNT(*)
----------
         5

SQL> select count(field1) from dan;
COUNT(FIELD1)
-------------
            3

SQL> select count(field2) from dan;
COUNT(FIELD2)
-------------
            2


So, still bear with me here.

counting a column with NULL in it, only returns the non-null count.

So, if you force a virtual column of 1, you never get nulls, and the count will always return the count of all rows in the table.

Re: Script for Table names and row counts [message #325045 is a reply to message #324980] Wed, 04 June 2008 22:09 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:470685000346811240
Re: Script for Table names and row counts [message #325047 is a reply to message #325031] Wed, 04 June 2008 22:15 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1156159920245

Re: Script for Table names and row counts [message #325097 is a reply to message #325031] Thu, 05 June 2008 00:32 Go to previous message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I know, I know, but its a habit

Don't spread it.
Quote:
I dont think there is any harm in it. (apart from some weird bug...

So there could be harm.
Quote:
Im just saying its down to coding style,

No it is a way of good developing thinking.
Quote:
unless Im proven it doesnt work,

What about the bug you mentioned?
Quote:
So, if you force a virtual column of 1, you never get nulls, and the count will always return the count of all rows in the table.

And what count(*) does?
The correct way is count(*), count(1) is a bad habit, keep it if you want but keep it FOR YOU, don't lead juniors to your error.

Regards
Michel
Previous Topic: PLSQL Procedure Error
Next Topic: how to write this sql query
Goto Forum:
  


Current Time: Wed Dec 07 18:15:58 CST 2016

Total time taken to generate the page: 0.08554 seconds