Results 1 to 5 of 5

Thread: Table size

  1. #1
    Join Date
    Sep 2006
    Posts
    6

    Unanswered: Table size

    Can someone give me query which will give
    me the size of individual tables in a database in MB.

    rgds

  2. #2
    Join Date
    Dec 2003
    Location
    North America
    Posts
    146
    You can get a rough estimate but running either of these in dbaccess, SQL editor, or your favorite SQL tool:

    select tabname, ((nrows * rowsize) / 1000000) size
    from systables
    where tabname = "table_name"

    or you could use this:

    NOTE: npused is "number of pages used" so you need to know your page size

    select tabname, ((npused * your_page_size) / 1000000) size
    from systables
    where tabname = "table_name"

    I prefer to use this one because pages are not 100% filled so there will usually be some unused space per page.

  3. #3
    Join Date
    Sep 2006
    Posts
    6
    Can u pls let me know how to find the nrows, rowsize
    also your_page_size coz im not sure if the one i hav is right.

    Rgds,
    Anthony.


    Quote Originally Posted by mjldba
    You can get a rough estimate but running either of these in dbaccess, SQL editor, or your favorite SQL tool:

    select tabname, ((nrows * rowsize) / 1000000) size
    from systables
    where tabname = "table_name"

    or you could use this:

    NOTE: npused is "number of pages used" so you need to know your page size

    select tabname, ((npused * your_page_size) / 1000000) size
    from systables
    where tabname = "table_name"

    I prefer to use this one because pages are not 100% filled so there will usually be some unused space per page.

  4. #4
    Join Date
    Dec 2003
    Location
    North America
    Posts
    146
    The values for "nrows" and "rowsize" can be found (for each specific table) in the table called systables for the database you're querying.

    You can determine the page size by running onstat -b and looking at the last line of the output. The page size is the same as buffer size which will be either 2048 (2K pages) or 4096 (4K pages).

  5. #5
    Join Date
    Apr 2013
    Posts
    1
    Hi
    I just query my systable and found the below data:

    unitydyndb> select nrows , rowsize , npused , pagesize from systables where tabid='104';
    nrows rowsize npused pagesize
    ------- ------- ------ --------
    25690.0 931 6324.0 2048

    now if i multiply rowsize*nrows then i will get 24MB as my table size.
    and if i multiply npused*pagesize then i will get 12.9MB as my table size.

    According to me, the npused*pagesize should always be greater or equal to rowsize*nrows. but why in my case it is just opposite.

Posting Permissions

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