Finding database and table sizes with sp_spaceused

There is a handy system stored procedure called sp_spaceused that can help when you need to know the space usage of a database or table within a database. There are two ways you can use it. The first way gives some basic size information about the database itself. There are no parameters and it returns two result sets.

EXEC sp_spaceused;

This call will yield something similar to the following.

The other way to use the procedure will give you information about a particular table within the database.

EXEC sp_spaceused N'Person.Address';

When using sp_spaceused for a single table there is only one result set.

As you will notice, this procedure can only get space usage about one table at a time. There are a few techniques to get the statistics about all of the tables in a database. One is to create a temp table and use the undocumented procedure sp_msforeachtable to run sp_spaceused for each table. Rebecca Lewis has an example of this. One issue with this technique is that the result doesn’t include the schema, so if you have multiple tables with the same name that exist in different schemas, you won’t be able to tell them apart.

There are other techniques around that don’t use sp_spaceused that can give you information all in one query. One example is on Luke¬†Hayler’s blog. Taking it a step further,¬† has an artcle on SQLServerPerformance.com that explains a way to track table growth.

Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *