SET feedback OFF heading OFF termout OFF

/*
COUNTER.SQL
This query developed in response to an Oracle Database Forums query asking "How can I develop a query which will list owners and table_names and then count the records in each?

Answer the two prompts that appear: 1) for OWNER (the default if you do not answer is '%INV%') and 2) for TABLE_NAME (the default there is '%ITEMS%', my favorite).

I SUGGEST THAT YOU RUN WITH DEFAULTS THE FIRST TIME YOU RUN THIS QUERY.
DON'T PICK '%' and '%' UNLESS YOU ARE READY TO WAIT !!

If you REALLY want to count ALL owners and ALL tables you will have to deliberately enter '%' at each of these prompts. Don't blame me if your DBA calls to ask what you are doing !

The query generates another file called CLN_COUNTER.SQL in your default directory, so check first to be sure that you don't have another query with that name (probably unlikely that we share the same initials and name our files this way, I suppose).
*/

SET termout ON
ACCEPT v_own PROMPT "Choose an OWNER now (default INV, enter % for ALL): "
ACCEPT v_tab PROMPT "Please limit your TABLE_NAMEs now, or % for ALL: "

SPOOL cln_counter.sql
--This file will be overwritten each time this query is run !

SELECT 'SELECT COUNT (*), ''' || owner || '.' || table_name
|| ''' FROM ' || owner || '.' || table_name || ', dual'
|| CHR(10) || '/'
FROM sys.all_tables
--WHERE owner LIKE UPPER( NVL( '%&&v_own%', 'INV'))
--AND table_name LIKE UPPER( NVL( '%&&v_tab%', 'ITEMS'))
--Of course, this (the two lines above) is the way to limit the query,
--but for some reason that stopped working properly after I added the
--comments at top.
--The two lines of coding below, while not as elegant, work.
WHERE owner LIKE UPPER( DECODE( '&&v_own', NULL, 'INV', '%&&v_own%'))
AND table_name LIKE UPPER( DECODE( '&&v_tab', NULL, '%ITEMS%', '%&&v_tab%'))
/

SPOOL OFF

@cln_counter
--Now running the query from the file you just generated with
--your query.

Return to : Oracle Database, SQL, Application, Programming Tips