Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2014
    Posts
    1

    Unanswered: SQL query to understand the names of all the available tables , number of records in

    Hi All,
    Please help with a SQL query to understand the names of all the available tables , number of records in these tables and size of these tables

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    When all else fails, Read The Fine Manual.

    Contents
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    all that are visible to you, all that are in your schema, or all that are in the database?

    USER_TABLES
    ALL_TABLES
    DBA_TABLES (only available to dba users)
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >size of these tables
    should the size of any associated INDEX be included?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    May 2014
    Location
    World Wide On The Web
    Posts
    16
    Quote Originally Posted by Devog View Post
    Hi All,
    names of all the available tables , number of records in these tables and size of these tables
    Code:
    SQL> column TABLE_NAME format a15;
    SQL> SELECT T.TABLE_NAME,
      2         T.NUM_ROWS,
      3         S.BYTES/1024/1024 MB
      4  FROM USER_TABLES T,
      5       USER_SEGMENTS S
      6  WHERE T.TABLE_NAME = S.SEGMENT_NAME
      7  /
    
    TABLE_NAME        NUM_ROWS         MB
    --------------- ---------- ----------
    T                               .0625
    SALGRADE                 5      .0625
    EMP                     14      .0625
    DEPT                     4      .0625
    Assumptions :
    1. Your statistics must be up to date to have the correct NUM_ROWS. See, for example above, table T shows NULL, though it has many rows.
    2. The table size is only the table data, as Anacedent asked you if you want to include the associated indexes or not?
    Regards,
    Lalit

Posting Permissions

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