Home » SQL & PL/SQL » SQL & PL/SQL » Querry about the name of table and no of records
Querry about the name of table and no of records [message #18879] Tue, 19 February 2002 19:12 Go to next message
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
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 Go to previous messageGo to next message
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;
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 Go to previous messageGo to next message
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
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 Go to previous messageGo to next message
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;
/
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 Go to previous messageGo to next message
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
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 Go to previous message
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;
Previous Topic: Re: how to use audit command
Next Topic: Kill the Session
Goto Forum:
  


Current Time: Fri Sep 24 06:49:07 CDT 2021