Check Database Table Size in SQL Server

In this SQL Server tutorial, you will understand how to check database table size in SQL Server.

Where you learn about the different system procedures or views to check the size of each table within any database. Also, you will see how to use SQL Server Management Studio to check the table size.

Check Database Table Size in SQL Server

You can use multiple ways to check database table size in SQL Server. You will see how to use the sp_spaceused and sp_MSforeachtable stored procedure to retrieve the database table size.

If you use the SQL Server Management Studio, you can view the table size by generating the standard reports about the database.

Also, there are views, sys.tables, sys.allocation_units, and sys.partitions that you can use to know the size of the tables.

Let’s move ahead and see how to check database table size in SQL Server.

Check Database Table Size in SQL Server Using sp_spaceused

SQL Server has a system procedure called sp_spaceused; this procedure returns information about the table, view, index size, unused rows, etc.

To know the size of the table, use the below query.

EXEC sp_spaceused 'tablename';
GO

Where,

• EXEC sp_spaceused: Executing the sp_spaceused procedure using the EXEC statement for a specific table.
• tablename: The name of the table whose size you want to know.

For example, open the command prompt or any SQL Server tool where you can execute the SQL query.

Select the database where your table exists using the below query.

USE E_commerce;
GO

View all the tables within the E_commerce database using the below query.

SELECT name from sys.tables;
GO

The E_commerce database contains different tables that you can see in the above query output.

Now, suppose you need to find the size of the CustomerOrders table. So, for that, use the below query.

EXEC sp_spaceused CustomerOrders;
GO

After the execution of the above query, you can see that the reserved space for the table is 72KB, and the table is using 16KB where data and index_size take space of 8KB each, Which means the total space consumed by the table is 8KB + 8KB which is equal to 16KB.

The unused space is 56KB, which you can calculate by 72KB-16KB. But the actual size of the table is 72KB.

This is how you can find the table size in a database using the sp_spaceused system view.

Check Database Table Size in SQL Server Using SSMS

If you want to check the table size using the SQL Server Management Studio, you can also do that.

First, open the SQL Server Management Studio and select the database, then right-click on the database that contains all the tables.

After right-clicking on the database, choose the Reports option and select Standard Reports. After that, select the Disk Usage by Table option, as shown in the picture below.

When you click on the Disk Usage by Table option, it shows the reports containing the size of each table in the database, as shown in the below picture.

The above picture shows the size of each table, which is 72KB, and each table uses only 16KB of space from the 72KB, but this size can differ depending on the number of records you have in your table or the indexes, etc.

Using the SSMS, this is how to check database table size in SQL Server.

Check Database Table Size in SQL Server Using sp_MSforeachtable

The sp_MSforeachtable is a stored procedure in the SQL Server. This procedure helps in iterating through all the tables in your database.

If you want to perform a specific operation on all the database tables, you can use this procedure and specify the action that you want to do with this procedure on each table. Generally, it is used for administrative and maintenance work.

Select the database using the below query.

Use E_commerce;

Then, run the below query to check the size of each table within the E_commerce database.

EXEC sp_MSforeachtable 'EXEC sp_spaceused [?]';

After executing the above query, it returns the result set containing the size of each table in the E_commerce database, which you can see in the above picture.

• Now understand the query, where the sp_MSforeachtable loops through all the tables in the E_commerce database and replaces the? Within the bracket with the actual table name.
• Then, for each table, the sp_spaceused procedure is executed to retrieve the size of the table.
• You can consider this ‘EXEC sp_spaceused [?]’ equal to ‘EXEC sp_spaceused [table_name]’ after each iteration for the tables in the database where table_name is replaced by the actual table name.

This is how you can use the sp_MSforeachtable to iterate through all the tables in a database and check database table size in SQL Server using the sp_spaceused procedure.

Check Database Table Size in SQL Server Using sys.allocation_units and sys.partitions

Here, you will use the sys.allocation_units, sys.partitions, and sys.tables system view to check the size of each table in the database. These views are combined to retrieve the information related to the tables, such as data, reserved, index, and unused space.

Let’s run the below query to check the table size.

USE E_Commerce;
SELECT
    tbl.NAME AS Table_Name,
    SUM(au.total_pages) * 8 AS 'TotalSpace in KB', 
    SUM(au.used_pages) * 8 AS 'UsedSpaceKB in KB', 
    (SUM(au.total_pages) - SUM(au.used_pages)) * 8 AS 'UnusedSpace in KB'
FROM
    sys.tables tbl
INNER JOIN
    sys.partitions prt ON tbl.object_id = prt.OBJECT_ID
INNER JOIN
    sys.allocation_units au ON prt.partition_id = au.container_id
GROUP BY
    tbl.Name
ORDER BY
    'TotalSpace in KB' Desc

From the above output, you can see that each table totals space, used and unused space in KB. For example, the Sales table has a total space of 72 KB, the used space is 16 KB, and the unused is 56 KB.

Now, you have known about the different ways that you can use to check database table size in SQL Server.

Conclusion

In this SQL Server tutorial, you learned how to check the size of tables in a database in sql server using the different stored procedures and sys views. Also learned how to use the SQL Server Management Studio to view the size of each table in a specific database.

You may also like:

• How to Create Temporary Table in SQL Server Management Studio?
• How to Create a Table in SQL Server Management Studio Using Query?

Bijay Kumar Sahoo

After working for more than 15 years in the Software field, especially in Microsoft technologies, I have decided to share my expert knowledge of SQL Server. Check out all the SQL Server and related database tutorials I have shared here. Most of the readers are from countries like the United States of America, the United Kingdom, New Zealand, Australia, Canada, etc. I am also a Microsoft MVP. Check out more here.