SQL Anywhere 11 - table size

Question

I am trying to get the table size for each table from a database using SQL Anywhere 11.

I just found out sp_spaceused has been deprecated

Any help with this would be greatly appreciated! :)

Insectivore

Do you have a link confirming it's deprecation or is this speculation? – Seductress

found one. dcx.sybase.com/1001/en/dbwnen10/wn-newjasper-s-3751424.html – Seductress

Sorry I haven't found a work around. Gave you +1 on question because googling it for 15 - 20 mins can't find a thing on resolution. – Seductress

Answer 1

Possibly the system view SYSTAB can be a good-enough alternative. It can give you the number of rows in the table, and it can give you how many pages the table uses. (In the sample below, I'm multiplying the number of pages by the DB's page size to get a total byte size.)

SELECT
    count,                      -- number of rows in the table
    (table_page_count * DB_PROPERTY('PageSize')) tablesize  
                                -- total size, in bytes
FROM SYSTAB
WHERE table_name = 'mytable';   -- or whatever limitations you want on 
                                -- the scope of the query

Hope this helps.

Heaviside

Answer 2

You can use this script at Sql Server to find the largest table in Database and row count

SELECT sc.name +'.'+ ta.name TableName
,SUM(pa.rows) RowCnt
FROM sys.tables ta
INNER JOIN sys.partitions pa
ON pa.OBJECT_ID = ta.OBJECT_ID
INNER JOIN sys.schemas sc
ON ta.schema_id = sc.schema_id
WHERE ta.is_ms_shipped = 0 AND pa.index_id IN (1,0)
GROUP BY sc.name,ta.name
ORDER BY SUM(pa.rows) DESC  

Messaline

Answer 3

To Add on to Dan K's answer.

SELECT
table_name,
count,                      
cast((table_page_count * DB_PROPERTY('PageSize')) as int) as Bytes,
cast(Bytes/1024 as varchar) + ' KB' as KB,
cast(Bytes/1024/1024 as varchar) + ' MB' as MB
FROM SYSTAB
WHERE creator = 1 
order by Bytes desc

Voracity