Anton Gochev’s Weblog

Is it possible to check what is allocated space per table, used space per table, space used by indexes and unused space per table in SQL Server 2005?

In the samples in this blog I’ll use SQL Server 2005 sample database named “AdventureWorks”.

Yes, it is possible. There is a stored procedure (SP) in SQL Server that allows us to see what is the used space, unused space and used space by indexes for a specific table. The name of this SP is sp_spaceused. According to documentation this procedure will return to us

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

It can be used to get db size information but in this sample we will use it to get a disk space used/allocated per table. If we execute the SP against this database for table store

   1: exec sp_spaceused ’sales.store’;

we will get the following result.

The picture above shows the information that we can receive for this specific table.

It looks nice but sometimes we need to have this information for all tables that a specific database contains. In such a case we can use a SP that will allow us to execute a query for all tables. This SP is called “sp_msforeachtable”. Unfortunately, this SP is not documented in msdn (or I wasn’t able to find anything about it) but you can find it in Programmability\Stored Procedures\System Stored Procedures of selected DB. Then you can right click on it (using SQL Server Management Studio) and to select modify. You can examine the body of this SP for more information. For our example we need to know that it will execute provided query against all tables in DB and will return one or more rows per each table (in our case one row). Note that we specify ‘?’ instead of table name. “sp_msforeachtable” SP will provide each table name as a parameter to our query. Let’s see.

   1: exec sp_msforeachtable @command1=

   2:          "exec sp_spaceused ‘?’";

Executing the SP we will get a result in SQL Server Management Studio like that below. The result contain part of the table results.

Well, this result is not really friendly and I do not think it is easy to be used in an application. What we can do to make the result more friendly? We can return it in one table. For this purpose we will create a temp table and will insert the result into it and then we will select stored data. We also need to check if the temp table already exists and to drop it (in our case). It is useful when you want to execute the query many times in SQL Server Management Studio. The sample is below.

   1: if object_id(‘tempdb..#usedSpace’) is not null

   2: begin

   3:     drop table #usedSpace;

   4: end

   5:  

   6: create table #usedSpace

   7: (

   8:    [name]   nvarchar(64),

   9:    [rows]   int,

  10:    [reserved]   nvarchar(64),

  11:    [data]   nvarchar(64),

  12:    [indexSize]   nvarchar(64),

  13:    [unused]   nvarchar(64),

  14: );

  15:  

  16: exec sp_msforeachtable @command1=

  17:          "insert into #usedSpace

  18:           exec sp_spaceused ‘?’";

  19:    

  20: select * from #usedSpace;

And part of the result.

Having all data in a temp table we can make some queries that will allow use to refine the output.