Tuesday, August 12, 2008

Displaying the Sizes of Your SQL Server's Database's Tables

"SQL Server has a handy little system stored procedure named sp_spaceused that will return the space used by a database or by a particular table. To determine the size used by the database, simply run:"
EXEC sp_spaceused
"This will return two result sets, the first one containing the database name, size, and unallocated space and the second containing a breakdown of the database's size into how much size is reserved and how much of that is taken up by data, how much by indexes, and how much remains unused."
"To return information about a particular table, simply call sp_spaceused passing in as the first parameter the name of the table. To determine the space used by the Orders table in the Northwind database use:"
EXEC sp_spaceused 'Orders'
This will return a single result set that provides the following information:
  • Name - the name of the table
  • Rows - the number of rows in the table
  • Reserved - amount of total reserved space for the table
  • Data - amount of space used by the data for the table
  • Index_Size - amount of space used by the table's indexes
  • Unused - amount of usused space in the table

"While sp_spaceused can be used to return the space usage for a single table, more than likely we want a web page where we can view the space used for all tables in a database. There are a number of ways to accomplish this, but in short we need to execute sp_spaceused once for every table in the database. In order to accomplish that we could do one of two things:"

  • Query the sysobjects table to get a list of tables in the database, then use a CURSOR to iterate through these table results one at a time, executing sp_spaceused for each one.
  • Use the undocumented sp_MSforeachtable stored procedure, which takes in a command and executes that command against all of the user tables in the database.

"The sp_MSforeachtable stored procedure is one of many undocumented stored procedures tucked away in the depths of SQL Server. A list of these handy stored procedures can be found at SQL Server 2000 Useful Undocumented Stored Procedures. In short, you can use sp_MSforeachtable like so:"

EXEC sp_MSforeachtable @command1="command to run"

"In the command to run put a ? where you want the table name to be inserted. For example, to run the sp_spaceused stored procedure for each table in the database, we'd use:"

EXEC sp_MSforeachtable @command1="EXEC sp_spaceused '?'"

This will execute EXEC sp_spaceused 'TableName' for each user table in the database.
"An alternative option is to create a stored procedure from which a temporary table is created and populated with one record for each result set returned by sp_spaceused. The entire results, then, can be returned to the ASP.NET page as a single result set by selecting the entire contents of the temporary table at the end of the stored procedure."

create table #spaceused (name varchar(128), rows bigint, reserved varchar(25), data varchar(25), index_size varchar(25), unused varchar(25))

EXEC sp_MSforeachtable @command1="insert into #spaceused EXEC sp_spaceused '?'"

select * from #spaceused

drop table #spaceused

The above SQL code courtesy of Toby.
Scott Mitchell is the the editor, founder, and primary contributor to 4GuysFromRolla.com.