-- Updated 11/8/12    added 'resolvedschemaId' qual to cursor to ignore Overlay forms
-- Completed DR  9/24/2012
-- This Script provides a count for all T tables in the current database
-- You might spool the output to a file for easier reading
-- 
-- Use At your own risk, and please, have a current backup of your system
-- This must be run on the ARSystem database
-- Execute the script by typing @<scriptname> at the SQL prompt

-- declaration of all variables  needed for processing

set linesize 300
drop table ARTableCount;


CREATE TABLE ARTableCount 
 (schemaid varchar2(5) not null, 
 schemaname varchar2(100) not null, 
 rowcounter number(15,0) not null);

DECLARE
	v_schemaid number(15,0); 
	v_SQLStatement varchar2(1000); 
	v_tablename varchar2(80);
	v_schemaname varchar2(100);


-- schema_cursor cycles through the schemaids in the arschema table


CURSOR schema_cursor IS
       select schemaid from arschema where schematype<=2 and schemaId=resolvedschemaId order by 1;
        
      

BEGIN


--Processing begins -- Do not edit after this line unless you are an experienced SQL programmer


    FOR v_schema_cursor IN schema_cursor LOOP

        v_schemaid := v_schema_cursor.schemaid; 
       
	select name into v_schemaname from arschema where schemaId=v_schemaid;
	v_SQLStatement := 'insert into ARTableCount select '||TO_CHAR(v_schemaid) ||', ' ||chr(39)||v_schemaname||chr(39)||', count(*) from T'||TO_CHAR(v_schemaid);
	EXECUTE IMMEDIATE v_SQLStatement;


        COMMIT;


    END LOOP;
END;
      	
/
	
select schemaId "Id", schemaname, rowcounter from ARTableCount order by rowcounter desc;