Get Size of Tables in SQL Server

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

copy
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