Step 2: Create the sql
script CreRunSchemaNumRows.sql
REM
=====================================================================
REM CreRunSchemaNumRows.sql
REM
=====================================================================
REM One select statement to get numrows info for all schema
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:\CreRunSchemaNumRows_Run.sql
c:\CreRunSchemaNumRows.tmp c:\CreRunSchemaNumRows.hta
SPOOL c:\CreRunSchemaNumRows_Run.sql
PROMPT
SPOOL c:\CreRunSchemaNumRows.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>Generate and
display Schema Num Rows</H1>
SET FEEDBACK OFF
PROMPT
SET ECHO ON HEADING ON
REM =====================================
REM
FOR ONE SCHEMA Change the owner below
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='HR') order
by owner,object_type,object_name;
PROMPT SPOOL
OFF
SPOOL OFF
PROMPT PROMPT </BODY></HTML>
@c:\CreRunSchemaNumRows_Run.sql
HOST "c:\windows\grep.exe -v
-e ignored -e ORA- -e SP2- -e ERROR -e SELECT
c:\CreRunSchemaNumRows.tmp > c:\CreRunSchemaNumRows.hta"
HOST START c:\CreRunSchemaNumRows.hta
exit
|
