When working with a database, it’s important to keep track of the size of tables, so that we can identify which tables are taking up the most space and optimize them accordingly.

In this tutorial, we’ll show you how to connect, get the size of tables in gigabytes and order them from larger to the smaller in three common database servers, MySQL, PostgreSQL and SQL Server.

MySQL

Step 1. Connect to the database

In order to connect to MySQL server using the command-line tools, you need to run the following command:

mysql -u username -p password -h hostname database_name

Replace username, password, hostname, and database_name with the appropriate values for your MySQL database.

Step 2. Get the Table Sizes

Once you’re connected to the database, you can use the following SQL query to get the size of each table:

SELECT *
FROM (SELECT TABLE_SCHEMA                                                AS TABLE_SCHEMA,
             TABLE_NAME                                                  AS TABLE_NAME,
             ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024 / 1024, 3) AS `Size (GB)`
      FROM INFORMATION_SCHEMA.TABLES
      WHERE TABLE_SCHEMA NOT IN ('INFORMATION_SCHEMA')) T
ORDER BY T.`Size (GB)` DESC;

Output Results:

+---------------+----------------------+------------+
| Table_Schema  | Table_Name           | Size (GB)  |
+---------------+----------------------+------------+
| eshop         | orders               | 10.43      |
| eshop         | customers            | 4.12       |
| eshop         | products             | 2.86       |
| eshop         | order_items          | 1.98       |
| music_app     | categories           | 0.24       |
| music_app     | reviews              | 0.05       |
+---------------+----------------------+------------+

Command-Line tools:

PostgreSQL

Step 1. Connect to the database

In order to connect to MySQL server using the command-line tools, you need to run the following command:

psql -h hostname -d database_name -U username -W

Replace hostname, database_name, and username with the appropriate values for your PostgreSQL database. You will be prompted to enter your password after running this command.

Step 2. Get the Table Sizes

Once you’re connected to the database, you can use the following SQL query to get the size of each table:

SELECT table_name AS `Table`,
ROUND(((data_length + index_length) / 1024 / 1024 / 1024), 2) `Size (GB)`
FROM information_schema.TABLES
WHERE table_schema = "database_name"
ORDER BY `Size (GB)` DESC;

Output Results:

+----------------------+------------+
| Table                | Size       |
+----------------------+------------+
| orders               | 10 GB      |
| customers            | 4 GB       |
| products             | 2.8 GB     |
| order_items          | 2 GB       |
| categories           | 240 MB     |
| reviews              | 50 MB      |
+----------------------+------------+

Command-Line tools:

SQL Server

Step 1. Connect to the database

In order to connect to SQL Server, you can use sqlcmd command-line tool and run the following command:

sqlcmd -S servername -U username -P password -d database_name

Replace servername, username, password, and database_name with the appropriate values for your SQL Server database.

Step 2. Get the Table Sizes

Once you’re connected to the database, you can use the following SQL query to get the size of each table:

SELECT 
    t.NAME AS TableName,
    s.Name AS SchemaName,
    p.rows AS RowCounts,
    SUM(a.total_pages) * 8 / 1024 / 1024 AS TotalSizeGB
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
LEFT OUTER JOIN 
    sys.schemas s ON t.schema_id = s.schema_id
WHERE 
    t.NAME NOT LIKE 'dt%' 
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255 
GROUP BY 
    t.Name, s.Name, p.Rows
ORDER BY 
    TotalSizeGB DESC;

Output Results:

+--------------------+---------------+-------------+--------------+
| TableName          | SchemaName    | RowCounts   | TotalSizeGB  |
+--------------------+---------------+-------------+--------------+
| orders             | dbo           | 10,000,000  | 10.43        |
| customers          | dbo           | 5,000,000   | 4.12         |
| products           | dbo           | 2,500,000   | 2.86         |
| order_items        | dbo           | 1,000,000   | 1.98         |
| categories         | dbo           | 250,000     | 0.24         |
| reviews            | dbo           | 50,000      | 0.05         |
+--------------------+---------------+-------------+--------------+

Command-Line tools:

Note

In case we want to display the results differently we can take into account the following calculations:
– Size in Gigabytes: 1024 / 1024 / 1024
– Size in Megabytes: 1024 / 1024
– Size in Kilobytes: 1024

Categorized in:

Notes

Tagged in:

, , ,