How to get all tables in informix database and their sizes in GB

informix

I can get a crude estimate of table sizes with 

SELECT tabname, (rowsize*nrows)/1024/1024/1024 AS tabsize 
FROM informix.systables 
ORDER BY tabsize DESC

Is there a way to get a more accurate measurement of the size of the tables?

Best Answer

There is a lot of nuances to this question. Like do you want the size of the total number of pages currently allocated for use (so this would include pages available to be used by the table...so pages reserved for use by only this table, but that currently don't have any data on them), or do you only want the size of the currently used pages? Do you want to include the size of any indexes that would be defined on the the table?

So here is a query that could be used that would get the size of any non-system catalog table in the current database connected to that is the size of all the space reserved for the table, not including detached indexes:

    select a.tabname, (b.nptotal * b.pagesize / 1024 / 1024 / 1024) as tabsize
    from sysmaster:systabnames a, sysmaster:sysptnhdr b, systables c
    where c.tabname = a.tabname and
    a.partnum = b.partnum and
    c.tabid > 99;

Sample output from my tiny test system (note that t3 is a fragmented table where 1 fragment has no data, so you could use sum and group by if you wanted to report at a table level rather then fragment level if the your schema included fragmented tables)

    tabname  t1
    tabsize  1.5258789063e-05

    tabname  t2
    tabsize  1.5258789063e-05

    tabname  t3
    tabsize  0.00000000000000

    tabname  t3
    tabsize  1.5258789063e-05

If you change the query to the following, it would change the results to only count the pages that are currently used for the table, not all the space that is reserved for use:

    select a.tabname, (b.npused * b.pagesize / 1024 / 1024 / 1024) as tabsize
    from sysmaster:systabnames a, sysmaster:sysptnhdr b, systables c
    where c.tabname = a.tabname and
    a.partnum = b.partnum and
    c.tabid > 99;

If you want the sizes of detached indexes to be included in the table sizes, you would then have to include sysfragments into the query as well (would probably be a union of 1 form of the above 2 queries along with something that then got the index partition information by also then including the sysfragments into the join as well)

Related Question