Determing SQL Server Table Size within a Database

A common problem that I have recently encountered was trying to identity which table within a database was the biggest one (in record size), therefore, taking up the most physical storage space. I found and modify this store procedure to work with MSSQL 2000, MSSQL 2005, and MSSQL 2008  that will perform all needed data calls and will return a result set with the data on all tables.

T-SQL Code:

 

USE DWDATA
GO
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¬†listingDECLARE¬†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

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.

Results:

 

 

 

About onlinejt

Blogger

,

No comments yet.

Leave a Reply

*