Re: SQL - Count of Rows into a table

Hi,

This isn't that complicated...

Use SQL*Plus.

Option I:

Run from the prompt:

INSERT INTO table_count
SELECT table_name, sysdate, num_rows FROM user_tables;

This only works when all tables are analyzed.

Option II:

Run from the prompt:

SELECT 'INSERT INTO table_count SELECT
'||table_name||',sysdate,'||count(*)||' FROM '||table_name||';';

spool the results and run the results.....

Maarten

RxB <rvbaker_at_mindspring.com> schreef in berichtnieuws 86quo9$ttd$1_at_nntp6.atl.mindspring.net...
> Hi
>
> I'm trying to write some SQL to record the rowcount from each of my
fifteen
> or so tables into a timestamped table. This is to provide me with history
> of the growth of
> my tables.
>
> Table def'n something like this
>
> Table - table_count
> (table_name varchar2(25)
> dated date default (sysdate)
> row_count number)
>
> I would like to automate the population of this to run on a daily basis.
>
> Can anyone help with this task..... I know I could generate a list of my
> tables from all_tables (where owner = 'FRED') and then step through them
> with a cursor, selecting the count(*), and then insert this number into
the
> table .... however this seems very 3GLish.
> There must be an easy relational way to do this in (possbily) one step.
>
> Hope someone can help, because I feel I'm re-inventing the wheel thnking
> about this !!!
>
> Rog
>
>
>
Received on Fri Jan 28 2000 - 03:17:24 CST