How to Retrieve the Size of All Tables in a Database

1. Overview

Monitoring the size of tables in a database is crucial for database administrators and developers. In particular, it helps ensure optimal performance and efficient storage usage and provides insights into how data grows over time. Furthermore, different relational database management systems (RDBMS) offer various methods to retrieve the size of all tables within a database. However, understanding these methods can significantly aid in database management tasks.

In this tutorial, we’ll explore how to retrieve the size of all tables in three popular databases: PostgreSQL, MySQL, and SQL Server. We’ll provide multiple methods to achieve the goal, including using built-in functions, querying system catalogs, and writing custom SQL queries. We’ll use the Baeldung University database schema for illustration purposes.

2. Retrieving the Size of All Tables in PostgreSQL

PostgreSQL provides several ways to retrieve the size of all tables in a database. Let’s explore each of the methods

2.1. Using INFORMATION_SCHEMA

INFORMATION_SCHEMA is a SQL-standard way to retrieve metadata about database objects. In PostgreSQL, the INFORMATION_SCHEMA.TABLES view provides information about tables in the database.

For example, we can retrieve the size of all the tables in the University database along with the pg_total_relation_size function:

SELECT
    table_schema,
    table_name,
    pg_size_pretty(pg_total_relation_size(table_schema || '.' || table_name)) AS table_size
 FROM
    information_schema.tables
 WHERE
    table_schema NOT IN ('information_schema', 'pg_catalog')
 ORDER BY
    pg_total_relation_size(table_schema || '.' || table_name) DESC;
+--------------+----------------------+------------+
| table_schema | table_name           | table_size |
|--------------+----------------------+------------|
| public       | teaching             | 104 kB     |
| public       | registration         | 56 kB      |
| public       | exam                 | 56 kB      |
| public       | course               | 56 kB      |
| public       | prerequisite         | 24 kB      |
| public       | student              | 24 kB      |
| public       | department           | 24 kB      |
| public       | program              | 24 kB      |
| public       | faculty              | 24 kB      |
| public       | specification        | 24 kB      |
| public       | studentgraduationgpa | 0 bytes    |
+--------------+----------------------+------------+
SELECT 11
Time: 0.031s

As seen in the output, the query returns the schema name, table name, and the size of each table in a human-readable format (e.g., KB, MB, GB). The pg_size_pretty function converts the size into a more understandable format. Then, the WHERE clause excludes system schemas like information_schema and pg_catalog from the results.

2.2. Using pg_table_size Function

The pg_table_size function is a built-in PostgreSQL function that returns the disk space used by the specified table, excluding the indexes and toast tables. For example, let’s get the size of all tables in the database.

We can query the pg_tables system catalogue and apply the pg_table_size function to each table:

SELECT
    schemaname AS schema_name,
    tablename AS table_name,
    pg_size_pretty(pg_table_size(schemaname || '.' || tablename)) AS table_size
 FROM
    pg_tables
 WHERE
    schemaname NOT IN ('information_schema', 'pg_catalog')
 ORDER BY
    pg_table_size(schemaname || '.' || tablename) DESC;
+-------------+---------------+------------+
| schema_name | table_name    | table_size |
|-------------+---------------+------------|
| public      | teaching      | 64 kB      |
| public      | registration  | 40 kB      |
| public      | exam          | 40 kB      |
| public      | course        | 40 kB      |
| public      | prerequisite  | 8192 bytes |
| public      | department    | 8192 bytes |
| public      | student       | 8192 bytes |
| public      | program       | 8192 bytes |
| public      | faculty       | 8192 bytes |
| public      | specification | 8192 bytes |
+-------------+---------------+------------+
SELECT 10
Time: 0.012s

Here, the query is similar to the previous one. But, it uses pg_tables instead of INFORMATION_SCHEMA.TABLES. The pg_table_size function is applied to each table to retrieve its size. Then, the results are ordered by table size, with the largest tables appearing first.

2.3. Using a Custom SQL Query with pg_stat_user_tables

Additionally, for more detailed analysis, we can create a custom SQL query that combines information from pg_stat_user_tables and pg_total_relation_size. Furthermore, the pg_stat_user_tables provides statistics for each user table in the database, and pg_total_relation_size can be used to get the total size of each table:

SELECT
    relname AS table_name,
    pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
    pg_size_pretty(pg_relation_size(relid)) AS data_size,
    pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid)) AS index_size
 FROM
    pg_stat_user_tables
 ORDER BY
    pg_total_relation_size(relid) DESC;
+---------------+------------+------------+------------+
| table_name    | total_size | data_size  | index_size |
|---------------+------------+------------+------------|
| teaching      | 104 kB     | 40 kB      | 64 kB      |
| exam          | 56 kB      | 8192 bytes | 48 kB      |
| registration  | 56 kB      | 8192 bytes | 48 kB      |
| specification | 24 kB      | 8192 bytes | 16 kB      |
| student       | 24 kB      | 8192 bytes | 16 kB      |
| department    | 24 kB      | 8192 bytes | 16 kB      |
| prerequisite  | 24 kB      | 8192 bytes | 16 kB      |
| program       | 24 kB      | 8192 bytes | 16 kB      |
| faculty       | 24 kB      | 8192 bytes | 16 kB      |
+---------------+------------+------------+------------+
SELECT 9
Time: 0.016s

The query retrieves the table name, total size, data size (without indexes), and index size for each user table, with the result stored by total size in descending order. This approach provides a more granular view of the space usage within each table.

3. Retrieving the Size of All Tables in MySQL

MySQL provides several ways to retrieve the size of tables within a database. Let’s explore each method.

3.1. Using INFORMATION_SCHEMA

We can retrieve the size of each table with the use of INFORMATION_SCHEMA:

SELECT
    table_schema AS 'Database',
    table_name AS 'Table',
    ROUND((data_length + index_length) / 1024 / 1024, 2) AS 'Size (MB)'
FROM
    information_schema.tables
WHERE
    table_schema = 'baeldung_university'
ORDER BY
    (data_length + index_length) DESC;
+------------+---------------+-----------+
| Database   | Table         | Size (MB) |
+------------+---------------+-----------+
| University | Exam          |      0.06 |
| University | Prerequisite  |      0.06 |
| University | Registration  |      0.06 |
| University | Specification |      0.06 |
| University | Teaching      |      0.06 |
| University | Course        |      0.05 |
| University | Faculty       |      0.05 |
| University | Program       |      0.05 |
| University | Department    |      0.03 |
| University | Student       |      0.03 |
+------------+---------------+-----------+
11 rows in set (0.045 sec)

Here, the query retrieves the database name, table name, and the size of each table in megabytes (MB). The data_length column represents the size of the data stored in the table, while the index_length column represents the size of the indexes.

3.2. Using SHOW TABLE STATUS

Furthermore, the SHOW TABLE STATUS command is another method to retrieve table sizes in MySQL. This command provides detailed information about each table in a database, including the size of the data and indexes:

SHOW TABLE STATUS FROM University;
+---------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
| Name          | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation          | Checksum | Create_options | Comment |
+---------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
| Course        | InnoDB |      10 | Dynamic    |    5 |           3276 |       16384 |               0 |        32768 |         0 |           NULL | 2024-07-18 16:48:49 | NULL        | NULL       | utf8mb3_general_ci |     NULL |                |         |
| Department    | InnoDB |      10 | Dynamic    |    5 |           3276 |       16384 |               0 |        16384 |         0 |           NULL | 2024-07-18 16:48:49 | NULL        | NULL       | utf8mb3_general_ci |     NULL |                |         |
| Exam          | InnoDB |      10 | Dynamic    |    0 |              0 |       16384 |               0 |        49152 |         0 |           NULL | 2024-08-12 15:09:25 | NULL        | NULL       | utf8mb3_general_ci |     NULL |                |         |
| Faculty       | InnoDB |      10 | Dynamic    |    0 |              0 |       16384 |               0 |        32768 |         0 |           NULL | 2024-08-12 15:09:20 | NULL        | NULL       | utf8mb3_general_ci |     NULL |                |         |
| Prerequisite  | InnoDB |      10 | Dynamic    |    0 |              0 |       16384 |               0 |        49152 |         0 |           NULL | 2024-08-12 15:09:20 | NULL        | NULL       | utf8mb3_general_ci |     NULL |                |         |
| Program       | InnoDB |      10 | Dynamic    |    0 |              0 |       16384 |               0 |        32768 |         0 |           NULL | 2024-08-12 15:09:20 | NULL        | NULL       | utf8mb3_general_ci |     NULL |                |         |
| Registration  | InnoDB |      10 | Dynamic    |    0 |              0 |       16384 |               0 |        49152 |         0 |           NULL | 2024-08-12 15:09:20 | NULL        | NULL       | utf8mb3_general_ci |     NULL |                |         |
| Specification | InnoDB |      10 | Dynamic    |    0 |              0 |       16384 |               0 |        49152 |         0 |           NULL | 2024-08-12 15:09:20 | NULL        | NULL       | utf8mb3_general_ci |     NULL |                |         |
| Student       | InnoDB |      10 | Dynamic    |    5 |           3276 |       16384 |               0 |        16384 |         0 |           NULL | 2024-07-25 15:54:34 | NULL        | NULL       | utf8mb3_general_ci |     NULL |                |         |
| Teaching      | InnoDB |      10 | Dynamic    |    0 |              0 |       16384 |               0 |        49152 |         0 |           NULL | 2024-08-12 15:09:20 | NULL        | NULL       | utf8mb3_general_ci |     NULL |                |         |
+---------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
11 rows in set (0.013 sec)

The result shows various columns such as Name, Engine, Row_format, Rows, Avg_row_length, Data_length, Max_data_length, Index_length, and more. Further, this approach shows more detailed information about the table sizes in the database.

4. Retrieving the Size of All Tables in SQL Server

SQL Server provides several ways to retrieve the size of all tables in a database. Let’s explore each of the methods.

4.1. Using sp_spaceused

The sp_spaceused system stored procedure in SQL Server provides a quick overview of the space used by a table. In particular, it returns the size of the data, indexes, and unused space for a specific table or the entire database:

EXEC sp_MSforeachtable 'EXEC sp_spaceused ''?''';
name                 rows    reserved    data       index_size  unused
-------------------- ------- ----------- ---------- ----------- -----
[dbo].[Course]       5       48 KB       16 KB      32 KB       0 KB
[dbo].[Department]   5       32 KB       16 KB      16 KB       0 KB
[dbo].[Departments]  4       32 KB       16 KB      16 KB       0 KB
[dbo].[Exam]         0       64 KB       16 KB      48 KB       0 KB
[dbo].[Faculty]      0       64 KB       16 KB      48 KB       0 KB
[dbo].[Prerequisite] 0       64 KB       16 KB      48 KB       0 KB
[dbo].[Program]      0       64 KB       16 KB      48 KB       0 KB
[dbo].[Registration] 0       64 KB       16 KB      48 KB       0 KB
[dbo].[Specification] 0      64 KB       16 KB      48 KB       0 KB
[dbo].[Student]      5       32 KB       16 KB      16 KB       0 KB
[dbo].[Teaching]     0       64 KB       16 KB      48 KB       0 KB
Time: 0.012s

In the output, the sp_spaceused command provides the number of rows, reserved space, data space, index size, and unused space for each table.

4.2. Using sys.dm_db_partition_stats

The sys.dm_db_partition_stats DMV provides detailed statistics about partitions, including the number of rows, reserved space, and used space for each partition. We can aggregate this information to retrieve the size of all tables in the database:

SELECT
    t.name AS table_name,
    SUM(a.total_pages) * 8 AS total_space_kb,
    SUM(a.used_pages) * 8 AS used_space_kb,
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS unused_space_kb
FROM
    sys.tables t
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
ORDER BY
    total_space_kb DESC;
table_name        total_space_kb  used_space_kb  unused_space_kb
----------------- --------------- -------------- ----------------
Teaching          64              16             48
Registration      64              16             48
Specification     64              16             48
Prerequisite      64              16             48
Program           64              16             48
Faculty           64              16             48
Exam              64              16             48
Student           32              16             16
Departments       32              16             16
Department        32              16             16
Course            32              16             16
Time: 0.02

As seen in the output, it provides the total space, used space, and unused space in kilobytes (KB) for each table in the database.

5. Conclusion

In this article, we’ve explored the various methods to retrieve table sizes from different RDBMS. Whether using PostgreSQL, MySQL, or SQL Server, each RDBMS provides various tools and methods to retrieve table sizes, enabling administrators to optimize performance, manage storage, and anticipate growth. Understanding these techniques is key to maintaining a well-performing and reliable database environment.