If you're running your web applications on a shared server, then you know all too well that often times there is a limit on the storage space for your database. So it is crucial to keep an eye on your storage so that you can avoid any serious downtime in the future.
Lucky for us, SQL Server makes it relatively easy to query for the size of each table by querying against various system views, which I will break down below.
But first, here is the query in its entirety.
SELECT
t.name AS TableName,
s.name AS SchemaName,
p.rows AS RowCounts,
SUM(a.total_pages) * 8 / 1024 AS TotalSpaceMB,
SUM(a.used_pages) * 8 / 1024 AS UsedSpaceMB,
(SUM(a.total_pages) - SUM(a.used_pages)) * 8 / 1024 AS UnusedSpaceMB
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
INNER JOIN
sys.schemas s ON t.schema_id = s.schema_id
WHERE
t.is_ms_shipped = 0
GROUP BY
t.name, s.name, p.rows
ORDER BY
TotalSpaceMB DESC;
Explanation of the query
Let's break down the views being queried and joined.
sys.tables - This view contains a row for every single table in your given database, along with any pertinent information related to that table. Note that it will only display tables that you have permission to read.
sys.indexes - This view contains a row for every index in your database. It's important to note that indexes can vastly improve performance when querying data, however they do come at a storage cost.
sys.partitions - This view contains a row for every partition of a table or an index. Which is essentially information on the storage and structure of a particular table and index.
sys.allocations - This view provides information about the storage allocation units used by database objects, such as tables and indexes.
sys.schemas - And lastly, this view contains a row for every schema in the database. Once again, assuming you have permission to view that particular schema.
Taking a look at the output
TableName - The system name given to the database table
SchemaName - The schema that the table belongs to
RowCounts - The total number of rows in the table
TotalSpaceMB - The total space allocated the table in megabytes.
UsedSpaceMB - The total space taken up by the table in megabytes.
UnusedSpaceMB - The space that is allocated, but not yet used by the table.
Frequent monitoring of your database tables is also a great way to measure how active certain parts of your applications are. You might launch a new feature only to realize that it is quickly growing in popularity and quickly requiring more storage space.
Walter Guevara is a software engineer, startup founder and currently teaches programming for a coding bootcamp. He is currently building things that don't yet exist.
Last updated on: