一、Oracle
SELECT *
FROM (SELECT t.*, ROWNUM rn
FROM (SELECT a.owner AS schema_name, a.table_name, b.comments as table_comment, COALESCE(a.blocks, 0) * 8 / 1024 AS size_kb
FROM dba_tables a left join dba_tab_comments b on a.table_name = b.table_name and a.owner = b.owner
WHERE a.owner = 'SH'
ORDER BY COALESCE(a.blocks, 0) DESC) t
) WHERE rn > 0 AND rn <= 10
二、MySQL
SELECT table_schema schema_name, table_name, table_comment, SUM(data_length) / 1024 AS size_kb
FROM information_schema.TABLES
WHERE table_schema = 'stock' and data_length is not null
GROUP BY table_name
ORDER BY SUM(data_length) DESC
LIMIT 10 OFFSET 0;
三、SQL Server
select a.*, e.value as table_comment from (SELECT s.name AS schema_name, t.name AS table_name,SUM(a.total_pages) * 8 AS size_kb
FROM sys.schemas s
JOIN sys.tables t ON s.schema_id = t.schema_id
JOIN sys.partitions p ON t.object_id = p.object_id
JOIN sys.allocation_units a ON p.partition_id = a.container_id
WHERE t.is_ms_shipped = 0 AND
p.index_id IN (0,1) AND
s.name = 'dbo'
GROUP BY s.name, t.name, p.rows
ORDER BY size_kb DESC
OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY) a left JOIN sys.extended_properties e ON table_name = OBJECT_NAME(e.major_id) and e.minor_id = 0;
四、GP或Pgsql
SELECT a.schemaname AS schema_name, a.relname AS table_name, cast(obj_description(b.relfilenode,'pg_class') as varchar) table_comment, pg_total_relation_size(a.relid) AS size_kb
FROM pg_catalog.pg_statio_user_tables a LEFT JOIN pg_class b on a.relid = b.oid
WHERE a.schemaname = 'public'
ORDER BY pg_total_relation_size(a.relid) DESC
LIMIT 10 OFFSET 0;
