July 27, 2007

Determing SQL Server Table Size

A common problem that I have recently encountered was trying to identify areas of my website database that were 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.


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.


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 a stored procedure execution.

The Script

    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
from dbo.sysobjects 
where  OBJECTPROPERTY(idN'IsUserTable'1

--A procedure level temp table to store the results

--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)
--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

--Get rid of the cursor
CLOSE tableCursor
DEALLOCATE tableCursor

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

--Final cleanup!


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.


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.


Click here to download a copy of the SQL Statement used to create this procedure.

tags: Tutorials, SQL
comments powered by Disqus

Content provided in this blog is provided "AS-IS" and the information should be used at your own discretion.  The thoughts and opinions expressed are the personal thoughts of Mitchel Sellers and do not reflect the opinions of his employer.

Content Copyright

Content in this blog is copyright protected.  Re-publishing on other websites is allowed as long as proper credit and backlink to the article is provided.  Any other re-publishing or distribution of this content is prohibited without written permission from Mitchel Sellers.