Table Sizes Script

if object_id('tempdb..#tempTableList') is not null  
drop table #tempTableList  
if object_id('tempdb..#tempresults') is not null  
drop table #tempresults  

DECLARE @totalrows INT
DECLARE @currentrow INT
DECLARE @currenttable NVARCHAR(255)
CREATE TABLE #tempTableList 
([id]         INT   IDENTITY(1,1),  
[schemaname] NVARCHAR(128),
[tablename]  NVARCHAR(128),
[row_count]    BIGINT,
[kb_reserved]   BIGINT,
[kb_data]    BIGINT,
[kb_index_size] BIGINT,
[kb_unused]   BIGINT) 

CREATE TABLE #tempresults (
[tablename]  NVARCHAR(128),
[row_count]    CHAR(11),
[kb_reserved]   VARCHAR(18),
[kb_data]    VARCHAR(18),
[kb_index_size] VARCHAR(18),
[kb_unused]   VARCHAR(18)) 

INSERT INTO #tempTableList ([schemaname], [tablename])
SELECT SCHEMA_NAME(O.SCHEMA_ID)  AS TABLE_SCHEMA,  
OBJECT_NAME(O.OBJECT_ID)  AS TABLE_NAME
FROM   SYS.TABLES O 

SELECT @totalrows = COUNT(*) FROM #tempTableList
SELECT @currentrow = 1

WHILE @currentrow <= @totalrows  
BEGIN  
DELETE #tempresults
SELECT @currenttable = [schemaname] + '.' + [tablename] FROM #tempTableList WHERE [id] = @currentrow
INSERT #tempresults EXEC sp_spaceused @currenttable
UPDATE #tempTableList
SET  [row_count]   = CAST(#tempresults.[row_count] AS BIGINT)
,[kb_reserved]  = CAST(REPLACE(#tempresults.[kb_reserved],' kb','') AS BIGINT)
,[kb_data]   = CAST(REPLACE(#tempresults.[kb_data],' kb','') AS BIGINT)
,[kb_index_size] = CAST(REPLACE(#tempresults.[kb_index_size],' kb','') AS BIGINT)
,[kb_unused]  = CAST(REPLACE(#tempresults.[kb_unused],' kb','') AS BIGINT)
FROM #tempTableList
INNER JOIN #tempresults ON #tempTableList.[tablename] = #tempresults.[tablename]

SET @currentrow = @currentrow + 1  
END  
SELECT * FROM #tempTableList
ORDER BY [schemaname],[tablename]