Is it possible to show the count of rows and size of tables in the HEAT Classic database?

Version 1

    Details

    Is it possible to show the count of rows and size of tables in the HEAT Classic database?


    Resolution

    The following SQL query will return a count of the rows and the space used and unused for each table in a database:


    SELECT 
    T.NAME AS TableName,
    P.ROWS AS RowCOUNTS,
    SUM(A.TOTAL_PAGES) * 8 AS TotalSpaceKB,
    SUM(A.USED_PAGES) *8 AS UsedSpaceKB,
    (SUM(A.TOTAL_PAGES) - SUM(A.USED_PAGES)) *8 AS UnusedSpaceKB
    FROM
    SYS.TABLES T
    INNER JOIN
      SYS.INDEXES I ON T.OBJECT_ID = I.OBJECT_ID
    INNER JOIN
      SYS.PARTITIONS P ON I.OBJECT_ID = P.OBJECT_ID AND I.INDEX_ID = P.INDEX_ID
    INNER JOIN
      SYS.ALLOCATION_UNITS A ON P.PARTITION_ID = A.CONTAINER_ID
    WHERE
    T.NAME NOT LIKE 'DT%'
    AND T.IS_MS_SHIPPED = 0
    AND I.OBJECT_ID > 255
    GROUP BY
    T.NAME, P.ROWS
    ORDER BY TOTALSPACEKB DESC