SQL Server – Get Table Sizes

The script below will retrieve the number of rows, reserved size, data size, index size and unused size. The​​ reserved size​​ is the overall size of the table (it is the total of data size, index size and unused size).

--DROP TABLE #tmpTableSizes

CREATE​​ TABLE​​ #tmpTableSizes

(

 ​​ ​​ ​​​​ tableName​​ varchar(100),

 ​​ ​​ ​​​​ numberofRows​​ varchar(100),

 ​​ ​​ ​​​​ reservedSize​​ varchar(50),

 ​​ ​​ ​​​​ dataSize​​ varchar(50),

 ​​ ​​ ​​​​ indexSize​​ varchar(50),

 ​​ ​​ ​​​​ unusedSize​​ varchar(50)

)

insert​​ #tmpTableSizes

EXEC​​ sp_MSforeachtable​​ @command1="EXEC sp_spaceused '?'"

select ​​​​ *​​ from​​ #tmpTableSizes

order​​ by​​ cast(LEFT(dataSize,​​ LEN(dataSize)​​ -​​ 4)​​ as​​ int) ​​​​ desc