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
0 comments:
Post a Comment