List Tables With More Than ‘X’ Rows

Description: List only tables that have more than ‘X’ number of rows.

-- 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
 
 
--Then use this query:
 
SELECT table_name, roucount(table_name) Records
FROM   cat
WHERE  roucount(table_name) >= 100;
/

Enjoyed this post? Share it!

 

Leave a comment

Your email address will not be published.