我正在尝试使用 SQL Anywhere 11 从数据库中获取每个表的表大小。
我刚刚发现sp_spaceused
已被弃用
任何这方面的帮助将不胜感激! :)
可能系统视图SYSTAB可能是一个足够好的替代方案。 它可以为您提供表中的行数,并且可以为您提供表使用的页数。 (在下面的示例中,我将页数乘以数据库的页面大小以获得总字节大小。
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
希望这有帮助。
可以在 Sql Server 上使用此脚本查找数据库和行计数中最大的表
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
补充一下Dan K的答案。
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