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.
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 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 Script
CREATE PROCEDURE GetAllTableSizesAS/*
Obtains spaced used data for ALL user tables in the database*/
DECLARE @TableName VARCHAR(100)
--For storing values in the cursor
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 CURSORFOR
select [name]from dbo.sysobjects
where OBJECTPROPERTY(id, N'IsUserTable') = 1FOR 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 @TableNameEND
--Get rid of the cursor
CLOSE tableCursorDEALLOCATE tableCursor
--Select all records so we can use the reults
SELECT * FROM #TempTable
--Final cleanup!
DROP TABLE #TempTableGO
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.
0 التعليقات:
Post a Comment