| Record Count [message #10663] |
Sun, 08 February 2004 23:11  |
Prasad KVV
Messages: 1
Registered: February 2004
|
Junior Member |
|
|
hi,
I need the report like
all the list if tables and its records count.
Thanks a& Regards
Prasad KVV
|
|
|
|
| Re: Record Count [message #10672 is a reply to message #10663] |
Mon, 09 February 2004 01:50  |
 |
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
If your tables have been recently analyzed, so that the num_rows column of the all_tables data dictionary contains the correct current information, then it is as simple as:
SELECT table_name,
num_rows
FROM all_tables
/
Otherwise, you can use either of the two options below:
STORE SET saved_settings REPLACE
SET ECHO OFF FEEDBACK OFF HEADING OFF PAGESIZE 0 VERIFY OFF
SPOOL query.sql
SELECT 'SELECT RPAD (''' || table_name || ''', 30),COUNT(*) FROM ' || table_name ||';'
FROM all_tables
/
SPOOL OFF
START query.sql
START saved_settings
-- or:
SET SERVEROUTPUT ON SIZE 1000000
DECLARE
v_count NUMBER;
BEGIN
FOR rec IN
(SELECT table_name
FROM all_tables)
LOOP
EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM "' || rec.table_name || '"'
INTO v_count;
DBMS_OUTPUT.PUT_LINE (RPAD (rec.table_name, 35) || v_count);
END LOOP;
END;
/
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
