How to Check the Size of Each Table in a Database

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.

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: 

You can use the following query to get the size of each table in megabytes: 

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: 

You can use the following query to get the size of each table in megabytes: 

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: 

You can use the following query to get the size of each table in megabytes: 

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: 

You can use the following query to get the size of each table in megabytes:

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;

Replace 'your_schema_name' with the name of your schema. 

These queries provide information about the size of each table in the specified database or schema, ordered by size in descending order. Adjust the queries based on your specific database system and requirements.