MYSQLにてテーブルサイズの調査を行う。


select
table_name, engine, table_rows as tbl_rows, avg_row_length as rlen,
floor((data_length+index_length)/1024/1024) as allMB,
floor((data_length)/1024/1024) as dMB,
floor((index_length)/1024/1024) as iMB
from information_schema.tables
where table_schema=database()
order by (data_length+index_length) desc;


+------------------+--------+----------+-------+-------+------+------+
| table_name | engine | tbl_rows | rlen | allMB | dMB | iMB |
+------------------+--------+----------+-------+-------+------+------+
| mt_searchlog | MyISAM | 2799447 | 146 | 418 | 390 | 27 |
| mt_entry | MyISAM | 9434 | 12629 | 114 | 113 | 1 |
| mt_log | MyISAM | 59543 | 257 | 17 | 14 | 3 |
| mt_entryphotoAll | MyISAM | 80260 | 47 | 5 | 3 | 1 |
| mt_trackback | MyISAM | 8908 | 278 | 2 | 2 | 0 |
| mt_template | MyISAM | 373 | 2870 | 1 | 1 | 0 |
| mt_placement | MyISAM | 9447 | 18 | 0 | 0 | 0 |
| mt_entryphoto | MyISAM | 9079 | 37 | 0 | 0 | 0 |
| mt_session | MyISAM | 2536 | 96 | 0 | 0 | 0 |
| mt_tbping | MyISAM | 493 | 473 | 0 | 0 | 0 |
| mt_rfdata | MyISAM | 256 | 288 | 0 | 0 | 0 |
| mt_plugindata | MyISAM | 7 | 3074 | 0 | 0 | 0 |
| mt_category | MyISAM | 151 | 52 | 0 | 0 | 0 |
| mt_errorlist | MyISAM | 19 | 572 | 0 | 0 | 0 |
| mt_blog | MyISAM | 4 | 379 | 0 | 0 | 0 |
| mt_tag | MyISAM | 5 | 22 | 0 | 0 | 0 |
| mt_templatemap | MyISAM | 12 | 77 | 0 | 0 | 0 |
| mt_author | MyISAM | 3 | 114 | 0 | 0 | 0 |
| mt_permission | MyISAM | 14 | 88 | 0 | 0 | 0 |
| mt_categoryinfo | MyISAM | 11 | 207 | 0 | 0 | 0 |
| mt_config | MyISAM | 1 | 80 | 0 | 0 | 0 |
| test2 | MyISAM | 2 | 7 | 0 | 0 | 0 |
| mt_accessranking | MyISAM | 0 | 0 | 0 | 0 | 0 |
| mt_comment | MyISAM | 0 | 0 | 0 | 0 | 0 |
| mt_notification | MyISAM | 0 | 0 | 0 | 0 | 0 |
| mt_objecttag | MyISAM | 0 | 0 | 0 | 0 | 0 |
| mt_fileinfo | MyISAM | 0 | 0 | 0 | 0 | 0 |
| mt_ipbanlist | MyISAM | 0 | 0 | 0 | 0 | 0 |
| mt_sendping | MyISAM | 0 | 0 | 0 | 0 | 0 |
+------------------+--------+----------+-------+-------+------+------+
29 rows in set (0.00 sec)

mysql>

■参考
http://opendatabaselife.blogspot.com/2009_08_01_archive.html
http://webmemo.uzuralife.com/category/pkweryrfvid.html/?search=&opt=0&order=0&from=380


SQL2000, SQL2005, SQL2008でテーブルサイズを調査するクエリー
テーブル名、行数、データサイズ(MB)


SELECT object_name(id)as 'Table Name' ,rowcnt as 'Number of Rows',
dpages as 'Number of Pages',(dpages * 8 )/1024 'サイズ(MB)'
FROM sysindexes
WHERE indid IN (1,0)
AND OBJECTPROPERTY(id, 'IsUserTable') = 1
ORDER BY 'サイズ(MB)' DESC