Results 1 to 6 of 6

Thread: Need a sql query or procedure or function

  1. #1
    Junior Member
    Join Date
    Jun 2007
    Answers
    5

    Need a sql query or procedure or function

    How to select all the tables from database where the tables are empty(no rows)
    for example database has 100 tables and 10 table have no rows.
    I would like to know those 10 table names

    Last edited by jakilati; 08-07-2007 at 05:29 PM.

  2. #2
    Expert Member
    Join Date
    Jun 2006
    Answers
    410

    Re: Need a sql query or procedure or function

    Here is an Oracle pl/sql for printing tables with no records,

    declare
    /*Cursor to fetch all table names in the current schema*/
    cursor table_csr is
    select table_name from user_tables;

    cnt number(10);
    l_sql varchar2(250);
    begin

    for table_rec in table_csr loop
    /*dynamic SQL which returns the row count*/
    l_sql := 'select count(*) into :l_cnt from ' || table_rec.table_name;
    execute immediate l_sql into cnt;

    if cnt = 0 then
    dbms_output.put_line(table_rec.table_name);
    end if;
    end loop;
    end;


  3. #3
    Moderator
    Join Date
    Jun 2007
    Answers
    2,074

    Re: Need a sql query or procedure or function

    Try to use the following

    SELECT TABLE_NAME, OWNER ,AVG_ROW_LEN FROM ALL_TABLES WHERE AVG_ROW_LEN=0


  4. #4
    Expert Member
    Join Date
    Jun 2006
    Answers
    410

    Re: Need a sql query or procedure or function

    Thanks dude...I have learnt a new concept today. Thanks a lot


  5. #5
    Junior Member
    Join Date
    Jun 2007
    Answers
    5

    Re: Need a sql query or procedure or function

    Thanks a lot to James and debasisdas.


  6. #6
    Junior Member
    Join Date
    Jun 2007
    Answers
    5

    Re: Need a sql query or procedure or function

    debasisdas or James please help me how to write the cursor which have to pickup all the data from user_tables and it has to write in a file format CSV.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
About us
Applying for a job can be a stressful and frustrating experience, especially for someone who has never done it before. Considering that you are competing for the position with a at least a dozen other applicants, it is imperative that you thoroughly prepare for the job interview, in order to stand a good chance of getting hired. That's where GeekInterview can help.
Interact