Home » SQL & PL/SQL » SQL & PL/SQL » list of tables containing no data
list of tables containing no data [message #321096] Mon, 19 May 2008 01:27 Go to next message
M.Shakeel Azeem
Messages: 226
Registered: September 2006
Senior Member
Dear All,

I want to findout the list of tables containing no data by writing a simple query.
Can anybody help me in this regard.

thanx in advance.

Re: list of tables containing no data [message #321100 is a reply to message #321096] Mon, 19 May 2008 01:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There is no simple way.
Either compute statistics for all tables and query user_tables.num_rows or get one of the multiple script that "select count(*)" from all tables.

Regards
Michel
Re: list of tables containing no data [message #321101 is a reply to message #321096] Mon, 19 May 2008 01:36 Go to previous messageGo to next message
varu123
Messages: 754
Registered: October 2007
Senior Member

Quote:
I want to findout the list of tables containing no data by writing a simple query.


The simplest way is
SQL> select owner,table_name from dba_tables
where num_rows=0;
Re: list of tables containing no data [message #321103 is a reply to message #321101] Mon, 19 May 2008 01:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
varu123 wrote on Mon, 19 May 2008 08:36

The simplest way is
SQL> select owner,table_name from dba_tables
where num_rows=0;


Only if the tables are analyzed.

Regards
Michel

Re: list of tables containing no data [message #321110 is a reply to message #321096] Mon, 19 May 2008 02:02 Go to previous messageGo to next message
M.Shakeel Azeem
Messages: 226
Registered: September 2006
Senior Member
thanx a lot Smile
Re: list of tables containing no data [message #321114 is a reply to message #321096] Mon, 19 May 2008 02:20 Go to previous messageGo to next message
msmallya
Messages: 66
Registered: March 2008
Location: AHMEDABAD, GUJARAT
Member
I had also same requiremnt some time back. Here is the query
(ofcourse using simply select count(*))

declare
tabrecs number(9):=0;
begin
    for r in (select tname from tab
              where tabtype='TABLE') loop
    execute immediate 'select count(*)'||' from '||r.tname
                        into tabrecs;
    if tabrecs=0 then
       dbms_output.put_line(r.tname||' = '||tabrecs);
    end if;
    end loop;
    exception when no_data_found then
    null;
end;


Regards,

MSMallya
Re: list of tables containing no data [message #321640 is a reply to message #321114] Tue, 20 May 2008 22:58 Go to previous messageGo to next message
harrysmall3
Messages: 109
Registered: April 2008
Location: Massachusetts
Senior Member
Thats the ticket -

the all_tab_cols table was once my best friend during a insurance claims conversion after a buy out. We were daunted with trying to find all unused or all-default/defunct columns for every table in a backend database used by our 3rd party repricing vendor that the buyers were relinquishing.

So I created a procedure to accept a table name, retrieve the columns and data types from the sys table, looped through each column per table and constructed
dynamic queries based on the data-type.

The defunct varchars were the pain - sometimes null, or a varied amount of spaces up to max....

Brings back memories!
Smile
Re: list of tables containing no data [message #321665 is a reply to message #321640] Wed, 21 May 2008 00:21 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And what this has to do with OP's question?

Regards
Michel
Re: list of tables containing no data [message #322233 is a reply to message #321665] Thu, 22 May 2008 20:25 Go to previous messageGo to next message
harrysmall3
Messages: 109
Registered: April 2008
Location: Massachusetts
Senior Member
Michel, per your response (statistics must be fresh for use of num_rows) I was indicating favor of MSMallya's post on
using dynamic SQL.

I guess I got overly enthusiastic and left out the application
of it back to the original question!

So to redeem myself from my verbose tangent:

I agree a straight forward independent approach would be to use execute immediate to process dynamic 'select count(*)'s from the tables.

Regards
Harry

Re: list of tables containing no data [message #322236 is a reply to message #321101] Thu, 22 May 2008 20:54 Go to previous messageGo to next message
sri2005_05
Messages: 4
Registered: May 2008
Junior Member
This sql doesnt work for transaction tables...as the rows of table keep on updating..the value of num_rows doesnt change..
Re: list of tables containing no data [message #322240 is a reply to message #321096] Thu, 22 May 2008 21:14 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>This sql doesnt work for transaction tables
If we to which SQL you refer, your response would be (more?) useful.
Re: list of tables containing no data [message #322241 is a reply to message #322240] Thu, 22 May 2008 21:15 Go to previous message
sri2005_05
Messages: 4
Registered: May 2008
Junior Member

sorry, i am refering to below sql

select owner,table_name from dba_tables
where num_rows=0;
Previous Topic: call procedure over db link
Next Topic: Database link not working for count(1) but works for count(*)
Goto Forum:
  


Current Time: Fri Dec 09 17:11:35 CST 2016

Total time taken to generate the page: 0.25070 seconds