In the intricate landscape of Database management, understanding the size of each Table is important for optimizing performance, managing storage efficiently, and making informed decisions about data storage. The size of tables directly influences the speed of queries, the allocation of resources, and the overall health of a database system. Whether you're a database administrator seeking to enhance performance or a developer fine-tuning applications, delving into the size metrics of your database tables is a fundamental step towards a well-optimized system.
Related Articles
In this article, we'll see how to check the size of each individual table in a Database using SQL statement for different database management systems.
For MySQL or MariaDB:
SELECT table_name AS "Table",
round(((data_length + index_length) / 1024 / 1024), 2) AS "Size (MB)"
FROM information_schema.tables
WHERE table_schema = 'your_database_name'
ORDER BY (data_length + index_length) DESC;
For PostgreSQL:
SELECT table_name AS "Table",
pg_size_pretty(pg_total_relation_size(table_name)) AS "Total Size"
FROM information_schema.tables
WHERE table_schema = 'public'
ORDER BY pg_total_relation_size(table_name) DESC;
For SQL Server:
SELECT t.NAME AS "Table",
s.NAME AS "Schema",
p.rows AS "Rows",
SUM(a.total_pages) * 8 / 1024 AS "Size (MB)"
FROM sys.tables t
INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id
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
GROUP BY t.NAME, s.NAME, p.rows
ORDER BY SUM(a.total_pages) DESC;
For Oracle:
SELECT table_name AS "Table",
ROUND((num_rows * avg_row_len) / 1024 / 1024) AS "Size (MB)"
FROM all_tables
WHERE owner = 'your_schema_name'
ORDER BY ROUND((num_rows * avg_row_len) / 1024 / 1024) DESC;