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

By Scott Mitchell

Introduction
In my day-to-day work as a writer, trainer, and consultant, I usually wear the ASP.NET developer hat, focusing on designing the middle-tier and front-end portions of ASP.NET applications with my exposure to the database usually being limited to data modeling and writing queries/stored procedures to access the data. The typical DBA activities - creating indexes, doing consistency checks, backing up the database, and so on - have always been someone else's responsibility. Over the past several months, however, I've been taking a more active role in the DBA side of things for a decent sized database, one with over 200 tables, close to 500 stored procedures, and chewing through a few gigabytes of disk space.

With these new responsibilities, I've been building up my arsenal of utility pages and helper programs for this role. For example, I have a program that periodically runs the SQL Profiler on the database for intervals of 10 minutes storing the results into a trace file that is later loaded to an offline database and denormalized for parsing and reporting on various database performance metrics. (Perhaps I'll write about this process in a future article.) Another utility that I use is a web page that quickly shows a summary of the tables in the database - namely the number of rows in the table and the size on disk the table and its indexes consume. The following shows the output when running this script against the Northwind database.

Name Rows Data Size Index Size
Categories 8 112 KB 32 KB
CustomerCustomerDemo 0 0 KB 0 KB
CustomerDemographics 0 0 KB 0 KB
Customers 91 24 KB 80 KB
Employees 9 232 KB 48 KB
EmployeeTerritories 49 8 KB 24 KB
Order Details 2,155 72 KB 208 KB
Orders 830 160 KB 320 KB
Products 77 8 KB 96 KB
Region 4 8 KB 24 KB
Shippers 3 8 KB 16 KB
Suppliers 29 24 KB 48 KB
Territories 53 8 KB 24 KB

The techniques I use work both in SQL Server 2000 and SQL Server 2005, and have been used in both ASP.NET 1.x and 2.0 pages. In this article we'll look at two ways to grab this information from SQL Server and how to display it in a web page. Read on to learn more!

- continued -

Determining the Space Used by a Database Table
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. For example, running sp_spaceused on the SQL Server 2005 Express Edition Northwind database that's included in this article's download, the following results are returned:

database_name database_size unallocated space

C:\MY PROJECTS\ASP.NET TEACHING\TUTORIAL 2.0 WEBSITES\SPACEUSED\APP_DATA\NORTHWND.MDF 4.12 MB 0.42 MB

reserved data index_size unused

3280 KB 1432 KB 1552 KB 296 KB

(Note that the database name is the same as the path to the SQL Server 2005 Express Edition database file; see Working with SQL Server 2005 Express Databases for more information on how SQL Server 2005 Express Edition databases are named.)

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

Running sp_spaceused on the Northwind database's Orders table results in the following output:

name rows reserved data index_size unused

Orders 830 504 KB 160 KB 320 KB 24 KB

As these numbers show, the Orders table's 830 rows take up 160 KB of space. The table's indexes take up an additional 320 KB. (There are nine such indexes on this table, hence the disproportion of index space to data space.) Along with 24 KB of unused data, the Orders table's data and indexes comprise 504 KB of the database's total size.

Returning the Space Used for All Tables
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.

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.

Part 2 of this article examines how to use these two techniques for displaying size information about a database's tables using a DataGrid or GridView. The download at the end of this article includes code samples for both techniques.

Read Part 2!