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 size of the table.
Check Database Table Size in SQL Server
To check database table size in SQL Server, there are multiple ways that you can use. You will see how to use the sp_spaceused and sp_MSforeachtable stored procedure to retrieve the database table size.
If you are using the SQL Server Management Studio, then 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 in combination 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
- 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 output of the query.
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 size of the table in a database using the sp_spaceused system view.
Check Database Table Size in SQL Server Using SSMS
If you want to check the size of the table 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 then select Standard Reports, after that, select the Disk Usage by Table option, as shown in the below picture.
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.
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 tables of the database, you can use this procedure and specify the action with this procedure that you want to do on each table. Generally, it is used for administrative and maintenance work.
Select the database using the below query.
Then, run the below query to check the size of each table within the E_commerce database.
EXEC sp_MSforeachtable 'EXEC sp_spaceused [?]';
After the execution of 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 retrieve the table size 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. Using these views in combination 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, 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.
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?
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.