테이블 별 용량을 확인하는 SQL 쿼리는 사용하는 데이터베이스에 따라 약간씩 다릅니다. 아래는 주요 데이터베이스에 대한 쿼리 예시입니다.
1. Oracle
SELECT
segment_name AS table_name,
segment_type,
bytes / 1024 / 1024 AS size_in_mb
FROM
user_segments
WHERE
segment_type = 'TABLE'
ORDER BY
size_in_mb DESC;2. MySQL
• MySQL에서는 information_schema.tables를 이용합니다
SELECT
table_schema AS database_name,
table_name,
ROUND(data_length / 1024 / 1024, 2) AS data_size_in_mb,
ROUND(index_length / 1024 / 1024, 2) AS index_size_in_mb,
ROUND((data_length + index_length) / 1024 / 1024, 2) AS total_size_in_mb
FROM
information_schema.tables
WHERE
table_schema = 'your_database_name'
ORDER BY
total_size_in_mb DESC;3. PostgreSQL
SELECT schemaname AS schema_name, relname AS table_name, pg_size_pretty(pg_total_relation_size(relid)) AS total_size, pg_size_pretty(pg_relation_size(relid)) AS table_size, pg_size_pretty(pg_indexes_size(relid)) AS index_size FROM pg_catalog.pg_statio_user_tables ORDER BY pg_total_relation_size(relid) DESC;
4. SQL Server
SELECT t.name AS table_name, s.name AS schema_name, p.rows AS row_count, (a.total_pages * 8) / 1024 AS total_size_mb, (a.used_pages * 8) / 1024 AS used_size_mb, (a.data_pages * 8) / 1024 AS data_size_mb FROM sys.tables t INNER JOIN sys.indexes i ON t.object_id = i.object_id AND i.index_id <= 1 INNER JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id INNER JOIN sys.schemas s ON t.schema_id = s.schema_id ORDER BY total_size_mb DESC;
5. MariaDB
(MySQL과 동일)
SELECT
table_schema AS database_name,
table_name,
ROUND(data_length / 1024 / 1024, 2) AS data_size_in_mb,
ROUND(index_length / 1024 / 1024, 2) AS index_size_in_mb,
ROUND((data_length + index_length) / 1024 / 1024, 2) AS total_size_in_mb
FROM
information_schema.tables
WHERE
table_schema = 'your_database_name'
ORDER BY
total_size_in_mb DESC;