Calculating Table Size in SQL Server

Calculating the size of tables in a SQL Server database is an essential task for database administrators and developers. It helps to identify which tables are consuming the most space, allowing for better storage planning and optimization. In this tutorial, we will explore different methods to calculate table sizes in SQL Server.

Method 1: Using System Views

SQL Server provides several system views that can be used to calculate table sizes. The sys.tables, sys.indexes, sys.partitions, and sys.allocation_units views contain information about tables, indexes, partitions, and allocation units, respectively. We can join these views to calculate the total space used by each table.

Here is an example query:

SELECT 
    t.name AS TableName,
    s.name AS SchemaName,
    p.rows,
    SUM(a.total_pages) * 8 AS TotalSpaceKB, 
    CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB,
    SUM(a.used_pages) * 8 AS UsedSpaceKB, 
    CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB, 
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB,
    CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) 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
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 
    TotalSpaceMB DESC, t.name

This query calculates the total space used by each table in kilobytes and megabytes, as well as the used and unused space.

Method 2: Using SQL Server Management Studio

SQL Server Management Studio (SSMS) provides a standard report to calculate disk usage by table. To access this report, follow these steps:

1. Right-click on the database in Object Explorer.
2. Navigate to Reports > Standard Reports > Disk Usage By Table.

Note that the database compatibility level must be set to 90 or above for this report to work correctly.

Method 3: Using Stored Procedures

SQL Server provides a stored procedure called sp_spaceused to calculate disk usage information for a table, indexed view, or the entire database. Here is an example:

USE MyDatabase; 
GO

EXEC sp_spaceused N'User.ContactInfo'; 
GO

This reports the disk usage information for the ContactInfo table.

To use this stored procedure for all tables at once, you can use the following query:

USE MyDatabase; 
GO

sp_msforeachtable 'EXEC sp_spaceused [?]' 
GO

Method 4: Using Object Explorer Details

In SSMS, you can also use Object Explorer Details to calculate table sizes. To do this:

1. Go to your database and select Tables in Object Explorer.
2. Open the Object Explorer Details page (either by pressing F7 or going to View > Object Explorer Details).
3. Right-click on the column header and enable the columns that you would like to see.

You can sort the data by any column and view the table sizes.

Conclusion

Calculating table sizes in SQL Server is an essential task for database administrators and developers. In this tutorial, we explored different methods to calculate table sizes using system views, SSMS, stored procedures, and Object Explorer Details. By using these methods, you can identify which tables are consuming the most space and optimize your database storage accordingly.