How do I get the tables sizes of my SQL database?

 

Recent Blogs << Back Extension Methods and Intellisense in VS 2008 10/7/2008 ProJobbers launches job postings 10/28/2008 Microsoft Excel alternating color rows / color ban 11/5/2008  More Blogs...

You can easily get Microsoft SQL Server database information to know what tables are your largest.

SET NOCOUNT ON
/*DATABASE TABLE SPY SCRIPT
 Micheal Soelter
 1/24/03
DESCRIPTION
 Returns TABLE Size Information 
SORTING USAGE
 @Sort bit VALUES
 0 = Alphabetically BY TABLE name
 1 = Sorted BY total space used by TABLE
*/
DECLARE @cmdstr varchar(100)
DECLARE @Sort bit
SELECT @Sort = 0 /* Edit this value FOR sorting options */
/* DO NOT EDIT ANY CODE BELOW THIS LINE */ 
--Create Temporary Table
CREATE TABLE #TempTable 
 (	[Table_Name] varchar(50),
	Row_Count int,
	Table_Size varchar(50),
	Data_Space_Used varchar(50),
	Index_Space_Used varchar(50),
	Unused_Space varchar(50)
 )
--Create Stored Procedure String
 SELECT @cmdstr = 'sp_msforeachtable ''sp_spaceused "?"'''
--Populate Tempoary Table
 INSERT INTO #TempTable EXEC(@cmdstr)
--Determine sorting method
IF @Sort = 0 


     BEGIN 
     --Retrieve Table Data and Sort Alphabet
    --     ically
     SELECT * FROM #TempTable ORDER BY Table_Name
 END

ELSE


     BEGIN
     /*Retrieve TABLE Data AND Sort BY the size OF the Table*/
     SELECT * FROM #TempTable ORDER BY Table_Size DESC
 END

--Delete Temporay Table
DROP TABLE #TempTable

Just run this script in a new query window. I didn't know why my database was so large. First I noticed my backups were getting very large. So I truncated my Log file because I noticed the .ldf was significantly larger than the .mdf file. But, then it was pretty large, so I narrows it down by running this script in SQL Server 2005.

 

http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=732&lngWId=5

11/27/2008 11:04:32 PM