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(idN’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: