In this instance, I'm using a cursor to loop through my tables and run sp_spaceused on them, and then put the results into a temporary table:
insert into #TableSizes exec sp_spaceused @objname = @name;
The problem is, when I select from the temp table:
select [Table Name]
, [Number of Rows]
, [Reserved Space]
, [Data Space]
, [Index Size]
, [Unused Space]
order by [Reserved Space] desc
the Reserved Space column isn't sorted correctly. I end up with something like this:
|Table Name||Number of Rows||Reserved Space|
Because Reserved Space is a varchar column, it sorts as text, based on the first character first. So 9's sort to the top, even small numbers like 96.
The correct solution is probably to trim off the " KB" from the end, cast to an integer, and sort by that:
order by cast(left([Reserved Space],len([Reserved Space])-3) as int) desc
which then also allows you to use SUM, etc. on the column.
I was in a hurry and didn't want to mess around with all the parenthesis, and came up with a quick and dirty solution:
order by len([Reserved Space]) desc,[Reserved Space] desc
This sorts the longest strings (hence the biggest numbers) to the top of the list, and then sorts each string alphabetically from 9 to 0.
Not something you'll need to do every day, but I thought it was a neat solution.