In this article we will learn how can we find out the list tables with table size and number of rows inside the table. There are two ways we have listed in this article.
As a DBA you may face many circumstances when you come on the stage that you have to shrink the database or clean up database.
When it comes to cleaning up database you have to find out which tables are very big in size and consuming more space on data file.
It is important to know the number of rows, the table size, data space allocated as well as the index space used. There are multiple ways that you can get such information. Easy way is by finding from SSMS.
There are other functions or tables from where we can find such information. Here in this article we will look at some queries that can be used to do this.
Table Size Using procedure : sp_spaceused
This method displays the number of rows, disk space reserved, and disk space used by a table, indexed view, or Service Broker queue in the current database, or displays the disk space reserved and used by the whole database.
exec sp_spaceused 'TableName'
exec sp_spaceused 'dbo.CustomerMst'
This stored procedure accepts only single table name. If we wish to get list of all tables, we have to put some extra efforts to get list of all tables with space used.
Looping on all tables with sp_msforeachtable
Lets use sp_msforeachtable to loop over all the tables in the database and we can pass additional command to be executed along with table name. Here is the example.
DECLARE @cmd VARCHAR(max) SET @cmd = 'exec sp_spaceused ''?''' EXEC sp_msforeachtable @cmd
Wow, we can see Number of Rows, reserved Space, Used space in KB, index size in KB and Unused space in KB for each table. But there is some problem in this command. It generates different result-sets for each table. We have to find something else to get everything in single Result-set.
Get result in Temp Table which is generated by sp_spaceused command.
--Create a Temp Table named #TableSpace CREATE TABLE #TableSpace ( TableName sysname ,TotRows BIGINT ,AllocatedSpace VARCHAR(50) ,UsedSpace VARCHAR(50) ,IndexSize VARCHAR(50) ,UnusedSpace VARCHAR(50) ) -- Loop through all tables using sp_msforeachtable and collect result into TempTable #TableSpace DECLARE @str VARCHAR(500) SET @str = 'exec sp_spaceused ''?''' INSERT INTO #TableSpace EXEC sp_msforeachtable @str --Run Select Query on #TableSpaace SELECT * FROM #TableSpace ORDER BY AllocatedSpace -- Delete temp table #TableSpace drop table #TableSpace
Wonderful, we got the result in single Result-set. Let’s understand, what we did in the command above.
- Create Temp Table with name – TableSpace
- Collect result into TableSpace temp table by using sp_msforeachtable with sp_spaceused
- Run select command on Temp table – TableSpace to get result.
- Drop temp table
Please you can not use sp_msforeachtable on Database hosted on Azure server and you may encounter error given below
Could not find stored procedure 'sp_msforeachtable'.
In such situation another method given below works good.
Table Size Using allocation_units System Table
This is a method is which is mostly preferred by many experienced DBAs during their database analysis work.
In this SQL command we will use 4 system tables to generate final result. It is really interesting.
- sys.tables : This table contains the list of system tables and user tables.
- sys.indexes : This table contains the list of all the indexes generated on all the tables by user or system
- sys.partitions : This table contains a row for each partition of all the tables and most types of indexes in the database.
- sys.allocation_units : Contains a row for each allocation unit in the database.
SELECT t.NAME AS TableName,p.rows TotalRows, SUM(a.total_pages) * 8 AS TotalSpaceKB, SUM(a.used_pages) * 8 AS UsedSpaceKB, (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB 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 GROUP BY t.Name, p.Rows ORDER BY TotalSpaceKB DESC, t.Name
If you are using SQL Server Management Studio (SSMS), instead of running a SQL query you can run a standard report by SSMS.
- Right click on the database
- Navigate to Reports > Standard Reports > Disk Usage By Table
Are you running out of space? Are you planning to Shrink database file? Read this article before doing shrink database file