データベースは、各テーブルに占めるスペースとレコード数を計算します.
2822 ワード
sql server
Mysql
Oracle
1 USE database_20160215
2 GO
3
4 SELECT
5 TableName = obj.name,
6 TotalRows = prt.rows,
7 [SpaceUsed(KB)] = SUM(alloc.used_pages)*8
8 FROM sys.objects obj
9 JOIN sys.indexes idx on obj.object_id = idx.object_id
10 JOIN sys.partitions prt on obj.object_id = prt.object_id
11 JOIN sys.allocation_units alloc on alloc.container_id = prt.partition_id
12 WHERE
13 obj.type = 'U' AND idx.index_id IN (0, 1)
14 GROUP BY obj.name, prt.rows
15 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