select non empty tables

[mkey]

Hi all,
I need to write a script that will select all the tables in a specific
schema
where the tables are not empty.
Any thoughts?

Thanks!

 

[mkey]

This script will do it!
SET SERVEROUTPUT ON SIZE 1000000
DECLARE
v_table VARCHAR2(30);
n_CountEntry PLS_INTEGER;
BEGIN
FOR i IN (SELECT table_name FROM user_tables) LOOP
v_table := i.table_name;
EXECUTE IMMEDIATE
'SELECT COUNT(1) FROM ' ||v_table INTO n_CountEntry;
IF n_CountEntry > 0 THEN
DBMS_OUTPUT.PUT_LINE('Table: '||v_table||' has:
'||n_CountEntry||' row(s)');
END IF;
END LOOP;
END;
/

 

[sem]

I think that the Mkey's script is quite simple though inacceptable for real world: counting rows in a large table to check that it's not empty is very expencive task. So, I'd recommend you to write a couple of more lines:

declare
type t is ref cursor;
c t;
dummy integer;
begin
for f in (select table_name from user_tables) loop
open c for 'select 1 from '||f.table_name;
fetch c into dummy;
if c%found then
dbms_output.put_line(f.table_name);
end if;
close c;
end loop;
end;