Live ORACLE

rem -----------------------------------------------------------------------
rem Filename:   countall2.sql
rem Purpose:    List tables from schema with more than X rows
rem Date:       15-Sep-2005
rem Author:     Praveen Kumar Chugh
rem -----------------------------------------------------------------------

REM First of all create the following function - rowcount...

CREATE OR REPLACE FUNCTION rowcount(tname VARCHAR2) RETURN NUMBER IS
        x    NUMBER;
        stmt VARCHAR2(200);
BEGIN
        stmt := 'select count(*) from '||tname;
        execute immediate stmt into x;
        return x;
EXCEPTION 
        WHEN NO_DATA_FOUND THEN
                RETURN 0;
END;
/
SHOW ERRORS

REM Then write this query...

SELECT table_name, roucount(table_name) Records 
FROM   cat 
WHERE  roucount(table_name) >= 100;
/

List tables from schema with more than X rows