Returning the Space Used for All Tables in MSSQL Server Database

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. (Ick.)
• 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. (No need to write any CURSORs or query the sysobjects table!)

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. This, of course, returns a result set for each time sp_spaceused is invoked. Therefore, to process the results in an ASP.NET page you'd need to read these results into a SqlDataReader object and then use the NextResult() method to step through the result sets.