Snippet Name: List tables with more than 'X' rows Description: List only tables that have more than 'X' number of rows. Comment: (none) Language: PL/SQL Highlight Mode: PLSQL Last Modified: March 03rd, 2009
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; /