|
Step 2: Create the sql script
CreRunSchemaNumRowsAllDb.sql
REM
=====================================================================
REM CreRunSchemaNumRowsAllDb.sql
REM
=====================================================================
REM One select statement gets numrows info for all database
tables
REM
=====================================================================
REM Jerome Christopher [Jerome_christopher@yahoo.com]
REM
http://sqlhtm.blogspot.com
REM http://www.sqlhtm.com/sqlhtm
REM
=====================================================================
SET LINESIZE 280 PAGES 50 ECHO ON FEEDBACK ON HEADING ON TERMOUT
OFF
HOST DEL c:\CreRunSchemaNumRowsAllDb_Run.sql
c:\CreRunSchemaNumRowsAllDb.tmp c:\CreRunSchemaNumRowsAllDb.hta
SPOOL c:\CreRunSchemaNumRowsAllDb_Run.sql
PROMPT
SPOOL c:\CreRunSchemaNumRowsAllDb.tmp
PROMPT SET PAGESIZE 200
PROMPT SET LINESIZE 280
PROMPT SET FEEDBACK OFF
PROMPT SET TERMOUT OFF
PROMPT
SET HEADING OFF
PROMPT
PROMPT prompt
<HTML><HEAD>
PROMPT SELECT '<'||'style
type='||''''||'text'||'/'||'css'||''''||'>'|| chr(10)||chr(13)||
PROMPT 'body {font:7pt Arial,Helvetica,sans-serif;
color:black; background:}'||chr(10)||chr(13)||
PROMPT
'p {font:bold bold 8pt
Arial,Helvetica,sans-serif; color:darkblue;
background:}'||chr(10)||chr(13)||
PROMPT 'table {font:7pt
Arial,Helvetica,sans-serif; color:Black; background:#f7f7e7;
padding:0px 0px 0px 0px; margin:0px 0px 0px
0px;}'||chr(10)||chr(13)||
PROMPT 'tr
{font:7pt Arial,Helvetica,sans-serif; color:Black;
background:#f7f7e7; padding:0px 0px 0px 0px; margin:0px 0px 0px
0px;}'||chr(10)||chr(13)||
PROMPT 'td
{font:bold 12pt Arial,Helvetica,sans-serif; color:black;
background:; padding:0px 0px 0px 0px; margin:0px 0px 0px
0px;}'||chr(10)||chr(13)||
PROMPT 'th
{font:bold 8pt Arial,Helvetica,sans-serif; color:yellow;
background:black; padding:0px 0px 0px 0px;}'||chr(10)||chr(13)||
PROMPT 'h1 {font:bold 15pt
Arial,Helvetica,Geneva,sans-serif; color:black; background:silver;
}'||chr(10)||chr(13)||
PROMPT 'h2
{font:bold 9pt Arial,Helvetica,Geneva,sans-serif; color:black;
background:magneta; margin-top:2px;
margin-bottom:2px;}'||chr(10)||chr(13)||
PROMPT
'a {font:bold 12pt
Arial,Helvetica,sans-serif; color:#663300; background:lightgreen;
margin-top:0pt; margin-bottom:0pt;
vertical-align:top;}'||chr(10)||chr(13)||
PROMPT
'<'||'/'||'style>' FROM dual;
PROMPT /
PROMPT SET
MARKUP HTML ON ENTMAP OFF TABLE ""
PROMPT PROMPT
</HEAD><BODY>
PROMPT PROMPT <H1>NUMROWS FOR
ALL TABLES IN ALL SCHEMAS </H1>
SET FEEDBACK OFF
PROMPT SET ECHO ON HEADING ON
REM
===============================================================
REM NUMROWS FOR ALL TABLES IN ALL NON-SYS SCHEMAS.CHANGE AS
NEEDED
REM
===============================================================
SELECT 'SELECT '|| 'count(*),'||
''''||'<a style="background-color:yellow"> '||owner||''''||'
OWNER ' ||','|| ''''||'<a
style="background-color:"> '
||object_type||''''||' OBJ_TYPE '||','||
''''||'<a style="background-color:yellow">
' ||object_name||''''||' OBJ_NAME '||'
from '||owner||'.'||object_name ||';' from all_objects where
object_type in ('TABLE') and owner in (select
username from dba_users where
username<>'SYS') order by
owner,object_type,object_name;
PROMPT SPOOL OFF
SPOOL OFF
PROMPT
PROMPT </BODY></HTML>
@c:\CreRunSchemaNumRowsAllDb_Run.sql
REM
===============================================================================
REM The grep command below removes errors when this script is
run for all schemas.
REM
===============================================================================
HOST "c:\windows\grep.exe -v -e ignored -e ORA- -e SP2- -e ERROR
-e SELECT c:\CreRunSchemaNumRowsAllDb.tmp >
c:\CreRunSchemaNumRowsAllDb.hta"
HOST START c:\CreRunSchemaNumRowsAllDb.hta
exit
| 