to know the space used for all user tables in all databases

[shanghai2004]

Dear Expert,

By using stored procedure sp_spaceused, I created an sql script to list the spaces used by all tables in a particular database.
Now my boss asks me to modify the script to a stored procedure so it can list the spaces used by all tables in any given database.
I created a stored procedure, but it cannot pick the database I specified. It always picks master db because change in database context last only until the end of the EXECUTE statement. After executing exec (@db_cmd), the database is set backup to master. How do I solve this problem?

Thanks

==========================================
Create Procedure sp_db_spaceused
@dbname varchar(120)
as
declare @db_cmd varchar(150)
set @db_cmd = 'use ' + @dbname
exec (@db_cmd)

set nocount on

declare @Owner varchar(80)
declare @Tabla varchar(80)
declare @NomTabla varchar(80)

create table #tmpEspacio (
name varchar(60) not null,
Rows int not null,
Reserved_kb varchar(20) not null,
Data_kb varchar(20) not null,
Index_Size_kb varchar(20) not null,
Unused_kb varchar(20) not null,
Reserved_mb decimal(7,6) default 0.0,
Data_mb decimal(7,6) default 0.0,
Index_Size_mb decimal(7,6) default 0.0
)
declare tmpCur insensitive cursor for
select rtrim(so.name) as 'Tabla', rtrim(su.name) as 'Owner'
from sysobjects so (nolock) inner join sysusers su (nolock)
on so.uid = su.uid
where type = 'U'

open tmpCur

fetch next from tmpCur into @Tabla, @Owner

while @@Fetch_Status = 0 begin

set @NomTabla = @Owner + '.[' + @Tabla + ']'

insert #tmpEspacio (name, Rows, Reserved_kb, Data_kb,Index_Size_kb,Unused_kb)
exec sp_spaceused @NomTabla

fetch next from tmpCur into @Tabla, @Owner

end

close tmpCur

deallocate tmpCur

update #tmpEspacio
set Reserved_kb = left(Reserved_kb, len(Reserved_kb) - 3), Data_kb= left(Data_kb,
len(Data_kb) - 3),
Index_Size_kb = left(Index_Size_kb,len(Index_Size_kb) - 3), Unused_kb =
left(Unused_kb,
len(Unused_kb) - 3)

alter table #tmpEspacio
alter column Reserved_kb int

alter table #tmpEspacio
alter column Data_kb int

alter table #tmpEspacio
alter column Index_Size_kb int

alter table #tmpEspacio
alter column Unused_kb int

update #tmpEspacio
set Reserved_mb = Reserved_kb / 1024.0,
Data_mb = Data_kb/1024.0,
Index_Size_mb = Index_Size_kb/1024.0


select *
from #tmpEspacio where Rows <> 0
order by name

select sum(Reserved_mb) as 'Total Reserved(MB)',
sum(Data_mb) + sum(Index_Size_mb) as 'Total Used(MB)'
from #tmpEspacio where Rows <> 0

drop table #tmpEspacio

set nocount off

 

[manmaria]

May be it is a stupid answer but I can not resist to say.

Could you try adding

Use yourDBnamehere

before the procedure.