SQL query which list owners
and table names and then count the records in each
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.
Have a Oracle Question
Do
you have an Oracle Question?
Oracle Books
Oracle
Certification, Database Administration, SQL, Application, Programming Reference
Books
Oracle Application
Oracle
Application Hints and Tips
Oracle Home
Oracle
Database, SQL, Application, Programming Tips
All the site contents are Copyright © www.erpgreat.com
and the content authors. All rights reserved.
All product names are trademarks of their respective
companies.
The site www.erpgreat.com is not affiliated with or endorsed
by any company listed at this site.
Every effort is made to ensure the content integrity.
Information used on this site is at your own risk.
The content on this site may not be reproduced
or redistributed without the express written permission of
www.erpgreat.com or the content authors.
|