Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2004
    Posts
    3

    Unanswered: sql query: to display the number of records for all the tables listed in TABS table.

    We want to create a report to display the name and number of records of all the tables in the TABS table.
    e.g:
    Table Name Count
    EMP 10
    DEPT 5
    DUMMY 1

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Here is one I happen to have:
    Code:
    DECLARE
      t_command       varchar2(200);
      t_total_records integer;
      t_limit         integer := 0;    -- Only show tables with more rows
    BEGIN
      for r in (select table_name from user_tables order by table_name)
      loop
            t_command := 'SELECT COUNT(*) FROM '||r.table_name;
            EXECUTE IMMEDIATE t_command INTO t_total_records;
            if t_total_records > t_limit then
                    DBMS_OUTPUT.PUT_LINE(rpad(r.table_name,55,' ')||
                            to_char(t_total_records,'99999999')||' record(s)');
            end if;
      end loop;
    END;
    /

  3. #3
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    If your tables have been recently analyzed and you dont mind not having the exact number of records at the current point in time you can do the following without the overhead of count(*)

    select table_name, numrows from user_tables;

    Alan

  4. #4
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    Perhaps for completeness that should be USER_ALL_TABLES.

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Quote Originally Posted by WilliamR
    Perhaps for completeness that should be USER_ALL_TABLES.
    True, for those who might build object tables. That wouldn't include me

Posting Permissions

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