Determining SQL Server Table Size

A common problem that I have recently encountered was trying to identify areas of my website database that was taking up the most physical storage space. At work, I maintain a DotNetNuke installation that includes a 4.5 Gb reporting database and I was trying to pinpoint the exact sizes of ALL tables in my database and was having a few problems. I started using the "sp_spaceused" stored procedure but I had to run that for every table, I have over 200 tables in this database and it was just not feasible for me to do it this way. Therefore I wrote a stored procedure that will perform all needed data calls and will return a result set with the data on all tables. In this post I will share the script as well as a few interesting things I learned while writing it.

Environments

This stored procedure has been tested and used on a SQL Server 2000 instance, this may or may not work successfully on other versions of SQL Server, I have not yet had the time to test.

Overview

The process that the stored procedure goes through is very simple, first, we declare a cursor that will get the names of all user-defined tables in the current database. Then we create a temporary table to store the individual data elements for each table. Then we loop through the created cursor and save the results of the sp_spaceused command to our temporary table. The last steps include closing and deallocating the cursor, selecting all rows from out temp table and lastly dropping that table.

When I created this procedure the one item that I found to be very interesting was the INSERT [TABLE] EXEC [SP] portion. I have used INSERT INTO [TABLE] SELECT ... type statements before where I use a query to load data but never before had I loaded data into a table directly from the results of stored procedure execution.

The Script

CREATE PROCEDURE GetAllTableSizes
AS
/*
    Obtains spaced used data for ALL user tables in the database
*/
DECLARE @TableName VARCHAR(100)    --For storing values in the cursor

--Cursor to get the name of all user tables from the sysobjects listing
DECLARE tableCursor CURSOR
FOR 
select [name]
from dbo.sysobjects 
where  OBJECTPROPERTY(id, N'IsUserTable') = 1
FOR READ ONLY

--A procedure level temp table to store the results
CREATE TABLE #TempTable
(
    tableName varchar(100),
    numberofRows varchar(100),
    reservedSize varchar(50),
    dataSize varchar(50),
    indexSize varchar(50),
    unusedSize varchar(50)
)

--Open the cursor
OPEN tableCursor

--Get the first table name from the cursor
FETCH NEXT FROM tableCursor INTO @TableName

--Loop until the cursor was not able to fetch
WHILE (@@Fetch_Status >= 0)
BEGIN
    --Dump the results of the sp_spaceused query to the temp table
    INSERT  #TempTable
        EXEC sp_spaceused @TableName

    --Get the next table name
    FETCH NEXT FROM tableCursor INTO @TableName
END

--Get rid of the cursor
CLOSE tableCursor
DEALLOCATE tableCursor

--Select all records so we can use the results
SELECT * 
FROM #TempTable

--Final cleanup!
DROP TABLE #TempTable

GO

The above is the actual stored procedure, this must be created first then you simply call this one procedure to get the list of tables and sizes. At the bottom of this article, you will find a zip file available for download that will include this script file.

Usage

Now to get the table sizes for the current database you can simply run the following command.

EXEC GetAllTableSizes

The results of this procedure execution will show you the number of rows and physical sizes of each user-defined table in your database.