Display Table Sizes in MSSQL

-- Get all tables rows count, sizes, index size in MB
-- mReschke 2012-10-16
SET NOCOUNT ON
DECLARE @cmdstr VARCHAR(100)
CREATE TABLE #TempTable ([TABLE_NAME] VARCHAR(150), ROW_COUNT INT, Table_Size VARCHAR(150), Data_Space_Used VARCHAR(150), Index_Space_Used VARCHAR(150), Unused_Space VARCHAR(150))
CREATE TABLE #TempTable2 (TableName VARCHAR(150), [Rows] INT, DataMB INT, IndexMB INT, TotalMB INT, UnusedMB INT)
SELECT @cmdstr = 'sp_msforeachtable ''sp_spaceused "?"'''
INSERT INTO #TempTable EXEC(@cmdstr)
INSERT INTO #TempTable2
    SELECT
        [TABLE_NAME],
        ROW_COUNT,
        CONVERT(INTEGER, (SUBSTRING(Data_Space_Used,1, len(Data_Space_Used)-3)) / 1024) AS DataMB,
        CONVERT(INTEGER, (SUBSTRING(Index_Space_Used,1, len(Index_Space_Used)-3)) / 1024) AS IndexMB,
        CONVERT(INTEGER, (SUBSTRING(Table_Size,1, len(Table_Size)-3)) / 1024) AS TotalMB,
        CONVERT(INTEGER, (SUBSTRING(Unused_Space,1, len(Unused_Space)-3)) / 1024) AS UnusedMB
    FROM #TempTable
SELECT * FROM #TempTable2 ORDER BY [TableName] ASC
DROP TABLE #TempTable; DROP TABLE #TempTable2

DECLARE @table as varchar(150) SET @table = 'dbo.tblROTotal' --schema.tablename
SELECT i.name as IndexName, (s.used_page_count * 8) / 2014 as IndexSizeMB
FROM sys.dm_db_partition_stats s JOIN sys.indexes i ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id
WHERE s.[object_id] = object_id(@table)
ORDER BY IndexSizeMB DESC