Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> counting #rows in tables dynamically
a 3rd method would be to use dynamic sql to run count(*) on every table.
CREATE OR REPLACE FUNCTION numrows(
Ptable_name IN user_tables.table_name%TYPE)
RETURN NUMBER IS
/*
Program Name: Number of Rows Module Name : numrows.sql Written By : Daniel J. Clamage Description :
DBMS_OUTPUT.put_line('#rows in EMP: ' || numrows('emp'));END;
cnt NUMBER := 0; dummy INTEGER;
Then write a simple pl/sql block to run this routine for every table in the
schema:
declare
cnt number := 0;
begin
for t in (select table_name from user_tables) loop
cnt := cnt + numrows(t.table_name);
end loop;
dbms_output.put_line('total #rows in schema: ' || cnt);
end;
/
--
- Dan Clamage
http://www.telerama.com/~dclamage
If you haven't crashed the Server,
you haven't been trying hard enough.
Received on Wed Sep 02 1998 - 16:39:47 CDT