数据库计算每个表所占空间和记录数
sql server
USE database_20160215 GO SELECT TableName = obj.name, TotalRows = prt.rows, [SpaceUsed(KB)] = SUM(alloc.used_pages)*8 FROM sys.objects obj JOIN sys.indexes idx on obj.object_id = idx.object_id JOIN sys.partitions prt on obj.object_id = prt.object_id JOIN sys.allocation_units alloc on alloc.container_id = prt.partition_id WHERE obj.type = 'U' AND idx.index_id IN (0, 1) GROUP BY obj.name, prt.rows ORDER BY TableName
Mysql
/*这里面的单位是Byte*/ SELECT TABLE_NAME, DATA_LENGTH, INDEX_LENGTH, DATA_LENGTH + INDEX_LENGTH, TABLE_ROWS FROM information_schema. TABLES WHERE TABLE_SCHEMA = 'database_20160215';
Oracle
/*查某一用户下的表*/ select SEGMENT_NAME, TABLESPACE_NAME, sum(BYTES) || ' Bytes' from USER_extents where SEGMENT_TYPE = 'TABLE' group by SEGMENT_NAME, TABLESPACE_NAME /*查所有的表*/ select SEGMENT_NAME, TABLESPACE_NAME, sum(BYTES) || ' Bytes' from dba_extents where SEGMENT_TYPE = 'TABLE' group by SEGMENT_NAME, TABLESPACE_NAME