OraFAQ Forum: SQL & PL/SQL » Querry about the name of table and no of records

Home » SQL & PL/SQL » SQL & PL/SQL » Querry about the name of table and no of records

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Querry about the name of table and no of records [message #18879]

Tue, 19 February 2002 19:12

Tariq Zia lakho
Messages: 8
Registered: February 2002

Junior Member

hye me again.
I want to dispay the table name and total no of recrods
like

TABLE NAME NO OF RECORD
1. DEPT 4
2. EMP 14
3. SALGRADE 4

REGARDS

Report message to a moderator

Re: Querry about the name of table and no of records [message #18883 is a reply to message #18879]

Wed, 20 February 2002 00:22

pratap kumar tripathy
Messages: 660
Registered: January 2002

Senior Member

if your tables have been analyzed you can use this query.

select table_name,num_rows from user_tables;

Report message to a moderator

Re: Querry about the name of table and no of records [message #18887 is a reply to message #18879]

Wed, 20 February 2002 03:38

Suresh Vemulapalli
Messages: 624
Registered: August 2000

Senior Member

query which pratap suggested returns number of rows exist in table at the time of last analyzed. if u want approx. figures ,u can use that and it is fast also . but if u want exact count use script below.

1) save this script in a file (say count.sql)

set heading off
set pagesize 0
set echo off
set term off
set newpage none
spool cnt.sql
select 'prompt '||table_name ||chr(10)||'select count(*) from '||table_name||';' from
user_tables;
spool off
@cnt.sql

2)
SQL>@count.sql

Report message to a moderator

Re: Querry about the name of table and no of records [message #18889 is a reply to message #18879]

Wed, 20 February 2002 03:51

pratap kumar tripathy
Messages: 660
Registered: January 2002

Senior Member

suresh is right.

also u can try this method

set serveroutput on

declare
cursor table_cur is
select table_name
from user_tables;
num_rows integer;
begin
for tmp in table_cur
loop
execute immediate 'select count(rowid) from '||tmp.table_name
into num_rows;
dbms_output.put_line(tmp.table_name||'-------->' ||num_rows);
end loop;
end;
/

Report message to a moderator

Re: Querry about the name of table and no of records [message #18906 is a reply to message #18879]

Wed, 20 February 2002 19:02

Tariq Zia lakho
Messages: 8
Registered: February 2002

Junior Member

Thanks for reply me this Querry
table name and no of rows
but my dear
i have another problem inthis ,
when i write this
select table_name,num_rows from user_tables;
i retrieve few table rows but i recvd many tables
why it is not reply all table rows?
Regards
Lakho

Report message to a moderator

Re: Querry about the name of table and no of records [message #18910 is a reply to message #18879]

Wed, 20 February 2002 23:45

pratap kumar tripathy
Messages: 660
Registered: January 2002

Senior Member

i have clearly mentioned in my previous post ,you have to analyze the tables. som analyze all the tables in the schema as

analyze table table_name estimate statistics;

Report message to a moderator

Previous Topic:	Re: how to use audit command
Next Topic:	Kill the Session

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 26 00:55:29 CDT 2024