I don't know if there are any better methods to get it.
1 Create Procedure Rochelle sptablespaceused
2 As
3 Set Nocount On
4
5 Declare @ Result Table
6 (
7 Tablename Varchar ( 64 )
8 , [ Rows ] Int
9 , Reserved Varchar ( 64 )
10 , [ Data ] Varchar ( 64 )
11 , Index_size Varchar ( 64 )
12 , Unused Varchar ( 64 )
13 )
14
15 Declare @ Tablename Varchar ( 64 )
16
17 Declare Curtable Cursor Fast_forward For
18 Select [ Name ] From SYS. Tables Where [ Name ] Not Like ' Databaseschema % '
19
20 Open Curtable
21 Fetch Next From Curtable Into @ Tablename
22 While @ Fetch_status = 0
23 Begin
24 Insert Into @ Result
25 Exec Sp_spaceused @ Tablename
26 Fetch Next From Curtable Into @ Tablename
27 End
28 Close Curtable
29 Deallocate Curtable
30
31 Select *
32 From @ Result
33 Order By Cast ( Left ( [ Data ] , Len ( [ Data ] ) - 2 ) As Int ) Desc
34
35 Set Nocount Off