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 feasiable 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. EnvironmentsThis 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. OverviewThe 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 intersting 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 ScriptCREATE 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 reults
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. UsageNow 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. DownloadsClick here to download a copy of the SQL Statement used to create this procedure. |