create procedure sp_allspaceused
as
begin
declare @tablename varchar(200)
set nocount on
if object_id('tempdb..#tablename') is not null
drop table #tablename
create table #tablename (name varchar(200), rows int, reserved varchar(30), data varchar(30), index_size varchar(30), unused varchar(30))
declare tablecursor cursor for select name from sysobjects where xtype = 'U'
open tablecursor
fetch next from tablecursor into @tablename
while @@fetch_status = 0
begin
insert into #tablename execute sp_spaceused @tablename
fetch next from tablecursor into @tablename
end
close tablecursor
deallocate tablecursor
set nocount off
select * from #tablename order by
cast(replace(replace(reserved, ' KB', '000'), ' MB', '000000') as bigint) desc
end
