Getting Table Space Usage in SQL Server 2005

I had the need to determine how much space was being used by each of my tables in a certain schema. I found various solutions on the web, most of which involved the built in system stored procedure sp_spaceused. However none of them produced the output quite like I wanted it. Most worked on a single table, or else produced the output in multiple returns. So I did what comes naturally and cobbled together my own solution.It’s fairly straight forward. I create a temp table to hold the output, then create a cursor to cycle through all the table names in the schema set in the variable @schemaname. I set it to the most common one, dbo, but you may wish to change it. Next I cycle through each of the tables using a cursor and for each one call the sp_spaceused and insert the output as a new row in my temp table. Finally I select the results back to the user and drop the temp table to clean up.

You may note I check to see if the temp table exists as the very first thing, then if it is I drop it. Then as the last step I drop it. Call it paranoid programming, if something crashes part way through the routine I wanted the code to be “self repairing”, i.e. I didn’t want to have to remember to run the drop table statement just to start the procedure all over.

Also, you may have read somewhere that cursors in SQL Server are “evil”. Well, as a general rule you should avoid them in favor of set based coding. However there are times, such as this when I need to call a stored procedure for each row in the cursor, that a cursor will solve the issue nicely. Additionally this isn’t code I’m going to be running 500 times a day on a production server, but instead just occasionally as a system monitor type of utility.

You may notice that most of the columns are all returned as strings (varchars). That’s because the stored procedure returns them in that way. I may go back later and modify this to convert them to numbers so I can do some math, but we’ll see if that need arises.

Here’s the code, use it and abuse it, modify it and enjoy it!

/******************************************************************************

**    File: “GetTableSpaceUseage.sql”

**    Name: Get Table Space Useage for a specific schema

**    Auth: Robert C. Cain

**    Date: 01/27/2008

**

**    Desc: Calls the sp_spaceused proc for each table in a schema and returns

**        the Table Name, Number of Rows, and space used for each table.

**

**    Called by:

**     n/a – As needed

**

**    Input Parameters:

**     In the code check the value of @schemaname, if you need it for a

**     schema other than dbo be sure to change it.

**

**    Output Parameters:

**     NA

**

*******************************************************************************

**        Change History

*******************************************************************************

**    Date:            Author:                Description:

**    ——–        ——–            —————————————

**

*******************************************************************************/

 

/*—————————————————————————-*/

/* Drop the temp table if it’s there from a previous run                     */

/*—————————————————————————-*/

if object_id(N‘tempdb..[#TableSizes]’) is not null

  drop table #TableSizes ;

go

 

/*—————————————————————————-*/

/* Create the temp table                                                     */

/*—————————————————————————-*/

create table #TableSizes

  (

    [Table Name] nvarchar(128)   /* Name of the table */

  , [Number of Rows] char(11)    /* Number of rows existing in the table. */

  , [Reserved Space] varchar(18) /* Reserved space for table. */

  , [Data Space] varchar(18)    /* Amount of space used by data in table. */

  , [Index Size] varchar(18)    /* Amount of space used by indexes in table. */

  , [Unused Space] varchar(18)   /* Amount of space reserved but not used. */

  ) ;

go

 

/*—————————————————————————-*/

/* Load the temp table                                                        */

/*—————————————————————————-*/

declare @schemaname varchar(256) ;

— Make sure to set next line to the Schema name you want!

set @schemaname = ‘dbo’ ;

 

— Create a cursor to cycle through the names of each table in the schema

declare curSchemaTable cursor

  for select sys.schemas.name + ‘.’ + sys.objects.name

      from    sys.objects

            , sys.schemas

      where   object_id > 100

              and sys.schemas.name = @schemaname

              /* For a specific table uncomment next line and supply name */

            –and sys.objects.name = ‘specific-table-name-here’    

              and type_desc = ‘USER_TABLE’

              and sys.objects.schema_id = sys.schemas.schema_id ;

 

open curSchemaTable ;

declare @name varchar(256) ;  /* This holds the name of the current table*/

 

— Now loop thru the cursor, calling the sp_spaceused for each table

fetch curSchemaTable into @name ;

while ( @@FETCH_STATUS = 0 )

  begin    

    insert into #TableSizes

            exec sp_spaceused @objname = @name ;       

    fetch curSchemaTable into @name ;   

  end

 

— Important to both close and deallocate!

close curSchemaTable ;     

deallocate curSchemaTable ;

 

/*—————————————————————————-*/

/* Feed the results back                                                     */

/*—————————————————————————-*/

select [Table Name]

      , [Number of Rows]

      , [Reserved Space]

      , [Data Space]

      , [Index Size]

      , [Unused Space]

from    [#TableSizes]

order by [Table Name] ;

 

/*—————————————————————————-*/

/* Remove the temp table                                                     */

/*—————————————————————————-*/

drop table #TableSizes ;