12/10/2013 5:57:16 PM

Get a list of all tables in a SQL Server Database and the sizes of those tables.

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 BEGIN TRY INSERT #TempTable EXEC sp_spaceused @TableName END TRY BEGIN CATCH END CATCH --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 tableName, numberofRows, reservedSize, dataSize, indexSize, unusedSize, (CAST(REPLACE(dataSize, ' KB', '') AS FLOAT)) AS KBs, (CAST(REPLACE(dataSize, ' KB', '') AS FLOAT) / 1024) AS MBs FROM #TempTable ORDER BY MBs DESC --Final cleanup! DROP TABLE #TempTable