Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2004
    Posts
    9

    Unanswered: How to fine Empty Table in a USER

    Hi
    I have one query.. i.e., I have 15 tables in my database. In that 10tables having some rows and other is empty tables. How can i find empty tables.

    Plz help me...

    thanks in Advance...

    Chiranjeevi

  2. #2
    Join Date
    Dec 2003
    Location
    Oklahoma, USA
    Posts
    354
    You can run this... it will just output the table names of those having no rows:


    PHP Code:
    set echo off heading off feedback off lines 100 pages 0;

    spool tmp.sql
    select 
    'select ''' || table_name || ''' from ' || table_name || ' having count(*) = 0;' from user_tables;
    spool off;

    @
    tmp.sql 
    If you open the "tmp.sql" file, you'll see for all "15 tables"....

    PHP Code:
    select 'PERSONS' from PERSONS having count(*) = 0;
    select 'DEPARTMENT' from DEPARTMENT having count(*)=0;
    ... 
    but when the @tmp.sql is run... you'll just see table names of those tables with 0 rows.
    JoeB
    save disk space, use smaller fonts

  3. #3
    Join Date
    Dec 2003
    Location
    Oklahoma, USA
    Posts
    354
    Also, if your statistics are current... you can:

    SELECT table_name FROM user_tables WHERE num_rows=0;
    JoeB
    save disk space, use smaller fonts

Posting Permissions

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