Please help the SQL stupid
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Please help the SQL stupid

  1. #1
    Join Date
    Jan 2001
    Posts
    3,131
    Ok, I know this is an easy one but I am having a serious brain fart.

    I created a script that does a count(1) on all tables for a schema. Ex...

    select count(1) from jeff.myhero;
    select count(1) from jeff.palforlife;
    Ect...

    How do I get this to display the table_name, all this displays is count(1)
    100 rows
    but no table name.

    I played with the SQL* plus, verify, echo, termout, serveroutput but i got nada, suggestions?

    Thanks;
    MH
    I remember when this place was cool.

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    exec dbms_utility.analyze_schema('JEFF','COMPUTE');
    select table_name, num_rows
    from user_tables;
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  3. #3
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Are you generating your script from user_tables? If so, add a second column:
    select 'THE_ULTIMATE_MASTER' tabname, count(*) num_rows
    from jeff.the_ultimate_master;
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  4. #4
    Join Date
    Jan 2001
    Posts
    3,131
    Thanks Jeff;

    I thought about running an analyze but this is a production box and I'm not sure how much impact that will have or if it will screw up the optimizer or not. What say you?

    BTW this is Oracle 8.0.4.4.0 on HP/UX
    MH
    I remember when this place was cool.

  5. #5
    Join Date
    Jan 2001
    Posts
    3,131
    select 'select count(1) from SCHEMA.'||table_name||';'
    from dba_tables
    where owner='SCHEMA';

    This is how I generated the script, I spooled it.

    MH
    I remember when this place was cool.

  6. #6
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    select 'select ' || table_name || ', count(1) from SCHEMA.'||table_name||';'
    from dba_tables
    where owner='SCHEMA';
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  7. #7
    Join Date
    Jan 2001
    Posts
    3,131
    Originally posted by marist89
    select 'select ' || table_name || ', count(1) from SCHEMA.'||table_name||';'
    from dba_tables
    where owner='SCHEMA';
    Close but no cigar quiz kid, either I am doing something wrong or this SQL is schmutzed.

    Thanks though;

    MH
    I remember when this place was cool.

  8. #8
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    OK, OK.

    select 'select ''' || table_name || ''', count(1) from SCHEMA.'||table_name||';'
    from dba_tables
    where owner='SCHEMA';

    Surely, an OCP like yourself could have figured that out...
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  9. #9
    Join Date
    Jan 2001
    Posts
    3,131
    You have to admire a man that gets defensive when he SCREWS UP!!

    Thanks;

    Hugs and Kisses
    MH
    I remember when this place was cool.

  10. #10
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Originally posted by Mr.Hanky
    You have to admire a man that gets defensive when he SCREWS UP!!
    Your welcome?
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

Posting Permissions

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



Click Here to Expand Forum to Full Width