Page 1 of 2 12 LastLast
Results 1 to 15 of 16

Thread: Selecting All Rows in a Schema.

  1. #1
    Join Date
    Feb 2004
    Location
    Chester, NH
    Posts
    16

    Selecting All Rows in a Schema.

    Can someone help me write a SQL statmement that produces a return of all rows with a row count total in a given schema? Thanks!

  2. #2
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,922
    You want to generate select count(*) from statement for all tables?

    What dbms are you using?

  3. #3
    Join Date
    Feb 2004
    Location
    Chester, NH
    Posts
    16
    Yes.
    Oracle 8i

    I need 1 singular statement to: 1. return a list of all rows in a given schema (apps for example) and 2. post a row count total at the borttom. Thanks!

  4. #4
    Join Date
    Mar 2003
    Posts
    468
    rows of what?
    got any exmples of what you want?

    also, mixing column types ie. rows from a column and then a total at the bottom ususally means mixing in a procedure of some form or some interesting decodes or character conversions.

    if you can give us where the data is coming from and what the desired output should look like, that might help.

  5. #5
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,922
    You can get count of rows from dba_tables

    select DECODE(GROUPING(table_name), 1, 'Total',Table_Name) AS Table_Name, sum(num_rows) from dba_tables where owner=&yourschema
    group by rollup (table_name)
    /

  6. #6
    Join Date
    Mar 2003
    Posts
    468
    skhanal, just be cautious that if statistics haven't been run, there is no quarentee that the num_rows actually equal to the number of rows in the tables.

  7. #7
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,922
    Agreed.

  8. #8
    Join Date
    Feb 2004
    Location
    Chester, NH
    Posts
    16
    skhanal - the schema name is BOB - when I run the statement you gave me using that schema name it prompts me with "Enter value for bob:"

    select DECODE(GROUPING(table_name), 1, 'Total',Table_Name)
    AS Table_Name, sum(num_rows) from dba_tables
    where owner=&BOB
    group by rollup (table_name);

    I'm not sure what to enter for a value.

  9. #9
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,922
    Type BOB

    Or if you don't want a dynamic query use this

    select DECODE(GROUPING(table_name), 1, 'Total',Table_Name)
    AS Table_Name, sum(num_rows) from dba_tables
    where owner='BOB'
    group by rollup (table_name);

  10. #10
    Join Date
    Feb 2004
    Location
    Chester, NH
    Posts
    16
    skhanal, I'm getting closer, but there is no totals in the SUM column. And I know there are many rows in each of the 3 tables.

    SQL> select DECODE(GROUPING
    table_name), 1, 'Total',Table_Name)
    AS Table_Name, sum(num_rows) from dba_tables
    where owner='BOB'
    group by rollup (table_name);

    TABLE_NAME SUM(NUM_ROWS)
    ------------------------------ -------------
    T1
    TEST_01
    TEST_02
    Total

    SQL>

    Thanks! Keek

  11. #11
    Join Date
    Mar 2003
    Location
    Woking, UK
    Posts
    152
    As JKOOPMANN has mentioned, you must run statistics for all tables.

    the command is :
    ANALYZE TABLE table_name COMPUTE STATISTICS;
    or for whole schema use
    BEGIN
    DBMS_STATS.GATHER_SCHEMA_STATS('BOB');
    END;
    Last edited by YuckFou; 04-02-2004 at 02:09 AM.
    You Have To Be Happy With What You Have To Be Happy With (KC)

  12. #12
    Join Date
    Feb 2004
    Location
    Chester, NH
    Posts
    16
    skhanal, koop, & yuck, I'm set now. The gather stats did the trick!
    Thanks all!!

  13. #13
    Join Date
    Apr 2004
    Location
    Kansas City, Missouri, USA
    Posts
    4
    Yuck, you got funny name.


    Here's the script that does not need statistics. Will run on any version where dbms_sql is available.
    d

    DECLARE
    uname VARCHAR2(20) := 'BOB';
    rec_cnt NUMBER;
    tot_rec_cnt NUMBER := 0;
    stmt VARCHAR2(240);
    curs INTEGER;
    fdbk INTEGER;

    BEGIN
    dbms_output.put_line('User '||uname);
    dbms_output.put_line('Table, Record count');

    stmt := 'select count(*) from '||uname||'.$TABLE$';
    curs := dbms_sql.open_cursor;
    for rec in (select TABLE_NAME from all_tables where owner = uname)
    loop
    dbms_sql.parse(curs,replace(stmt,'$TABLE$',rec.tab le_name),dbms_sql.native);
    dbms_sql.define_column(curs,1,rec_cnt);
    fdbk := dbms_sql.execute_and_fetch(curs);
    dbms_sql.column_value(curs,1,rec_cnt);

    dbms_output.put_line(rec.table_name||','||rec_cnt) ;
    tot_rec_cnt := tot_rec_cnt + rec_cnt;
    end loop;
    dbms_output.put_line('Total records for '||uname||','||tot_rec_cnt);
    dbms_sql.close_cursor(curs);

    EXCEPTION WHEN OTHERS THEN
    dbms_sql.close_cursor(curs);
    dbms_output.put_line(stmt);
    dbms_output.put_line(SQLERRM);
    END;

  14. #14
    Join Date
    Mar 2003
    Location
    Woking, UK
    Posts
    152

    Hi all
    "Yuck" is not my name it's only nickname and for "funny" reasons I just added "Fou" and that's all story.
    You Have To Be Happy With What You Have To Be Happy With (KC)

  15. #15
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    When I first saw your name in Database Journal, I did some jumble immediately.

    It is quite amazing how some can change the officially unacceptable nick name to acceptable nick name.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •