Re: Counting from several tables

From: Fred <noway_at_jose.com>
Date: 3 Jun 2002 22:37:38 -0400
Message-ID: <20020603223738022-0400@news.his.com>

I have a similar need. What I've done is create a kind of "brute force" answer to my need. But the benefit is that I can now run this against almost ANY instance, and ANY schema. You could modify it to suit your needs as well.

The only requirement is that the user running the script has CREATE TABLE and DROP TABLE privileges:

REM This script will create a small table and populate it with REM row counts and table names
REM
REM It must be run as the user/schema owner being queried

undefine user passwd connect_string
connect &&user/&&passwd@&&connect_string

set linesize 128
set pagesize 9999
set trimspool on
set echo off
set heading off
set feedback off
set verify off
set termout off

drop table tabinfo
/
create table tabinfo (

	rowcount  number(8),
	tablename varchar2(30)
	)

/
spool tabcount.sql
select
  'insert into tabinfo values ((select count(*) from '||table_name||'), '''||table_name||''');'
from user_tables
/
spool off
@tabcount
set heading on
set feedback on
set termout on
col rowcount heading " No. Rows" format 99,999,999 col tablename heading "Table Name"
select * from tabinfo where rowcount>0 and tablename != 'TABINFO' /
set feed off
drop table tabinfo
/
set feed on

> "Keith C. Jakobs, MCP" <elohir_at_hotmail.com> wrote in message
> news:%kTK8.16136$153.271725735_at_newssvr14.news.prodigy.com...

>> Greetings:

>>

>> I am trying to make a SQL script that will tell me the the number of

> records

>> across several tables, listed by date.

>>

>> I would like my row data to show a range of dates, and the count of

> records

>> for each table as my columns, though I will transpose row and columns
>> if necessary (Can I use row data for column headings)?

>>

>> I know how to format my date comparisons, and I can do this script

> perfectly

>> for one table using the script below, but I have no idea how to make
>> one script, that will tally the count of all tables, in one output
>> table.

>>

>> SELECT TO_CHAR(logdate, 'MMDDYY') AS "Date", count(*) AS "Table1"
>> FROM table1
>> GROUP BY "Date";

>>

>> I need the counts of Table2, Table3, etc. to also show up in this
>> table.

>>

>> Can anyone help?

>>

>> Thanks in advance,

>>

>> Keith C. Jakobs, MCP
>> elohir_at_hotmail.com
Received on Mon Jun 03 2002 - 21:37:38 CDT