Try this ...
-- Amit
alter proc sp_MySpaceUsed_AllDB
as
declare @cmd varchar(255) , @db sysname
declare db cursor for
select name from master..sysdatabases where dbid > 4
if exists (select 1 from master..sysobjects where name = 'AllDBSpace')
drop table master..AllDBSpace
create table master..AllDBSpace (dbname sysname ,tabname varchar(60), rows varchar(100), reserved varchar(100), data varchar(100), index_size varchar(100), dataperrow varchar(100))
open db
fetch db into @db
while @@fetch_status =0
begin
set @cmd = 'exec '+ @db + '..sp_MySpaceUsed '
execute (@cmd)
fetch db into @db
end
close db
deallocate db
select * from AllDBSpace
go
alter proc sp_MySpaceUsed
as
/*******************************************************************************
written by : simon sabin
date : 25 october 2002
description : returns the spaceused by all tables in a database
:
history
date change
------------------------------------------------------------------------------
25/10/2002 created
19/02/2004 added database name in select list
*******************************************************************************/
set nocount on
declare @setoption bit, @databasename varchar(30), @ordercol varchar(30), @numeric bit
/*******************************************************************************
--change this to change the way data is ordered
*******************************************************************************/
select @ordercol = 'data'
select @databasename = db_name()
select @numeric = 1
if @databasename <> 'master'
and not exists (select 1 from master..sysdatabases where name = 'master' and (status & 4) = 4)
begin
exec sp_dboption @databasename ,'select into/bulkcopy', 'true'
select @setoption = 1
end
if exists (select 1 from master..sysobjects where name = 'space1')
drop table master..space1
create table master..space1 (name varchar(60), rows varchar(11), reserved varchar(11), data varchar(11), index_size varchar(11), unused varchar(11))
declare @cmd varchar(255)
declare cspace cursor for
select 'use ' +@databasename + ' insert into master..space1 exec sp_spaceused ''[' + u.name + '].[' + o.name + ']'''
from sysobjects o
join sysusers u on u.uid = o.uid
where type = 'u'
and o.name <> 'space1'
open cspace
fetch cspace into @cmd
while @@fetch_status =0
begin
-- print @cmd
execute (@cmd)
fetch cspace into @cmd
end
deallocate cspace
select @ordercol = 'data'
insert AllDBSpace
select db_name(), description,
rows,
reserved,
data,
index_size,
dataperrows
from (
select 3 dataorder,
convert(int,case @ordercol when 'rows' then rows
when 'reserved' then substring(reserved, 1,len(reserved)-2)
when 'data' then substring(data, 1,len(data)-2)
when 'index_size' then substring(index_size, 1,len(index_size)-2)
when 'unused' then substring(unused, 1,len(unused)-2) end) orderdata,
name description,
rows,
case @numeric when 0 then reserved else substring(reserved, 1, len(reserved)-2) end reserved,
case @numeric when 0 then data else substring(data, 1, len(data)-2) end data,
case @numeric when 0 then index_size else substring(index_size, 1, len(index_size)-2) end index_size,
--substring(data, 1, len(data)-2) dataperrows
--convert(numeric(19,6),substring(data, 1, len(data)-2)) /rows dataperrows
case when rows = 0 then '' else convert(varchar(11),convert(numeric(10,2),convert(numeric,substring(reserved, 1, len(reserved)-2)) /rows*1000)) end dataperrows
from master..space1
union all
select 1 dataorder, 0 orderdata,
convert(varchar(30),'total' ) description,
convert(varchar(11),sum(convert(int,rows))) rows,
convert(varchar(11),sum(convert(int,substring(reserved, 1,len(reserved)-2)))) reserved,
convert(varchar(11),sum(convert(int,substring(data, 1,len(data)-2)))) data,
convert(varchar(11),sum(convert(int,substring(index_size, 1,len(index_size)-2)))) index_size,
''
from master..space1
union all
select 2, 0,
replicate('-',30),
replicate('-',11),
replicate('-',11),
replicate('-',11),
replicate('-',11),
replicate('-',11)
union all
select 4,0,
replicate('-',30),
replicate('-',11),
replicate('-',11),
replicate('-',11),
replicate('-',11),
replicate('-',11)
union all
select 5,0,
convert(varchar(30),'total' ) description,
convert(varchar(11),sum(convert(int,rows))) rows,
convert(varchar(11),sum(convert(int,substring(reserved, 1,len(reserved)-2)))) reserved,
convert(varchar(11),sum(convert(int,substring(data, 1,len(data)-2)))) data,
convert(varchar(11),sum(convert(int,substring(index_size, 1,len(index_size)-2)))) index_size,
''
from master..space1 ) stuff
order by dataorder, orderdata desc, description
execute ('drop table master..space1')
if @setoption = 1
exec sp_dboption @databasename ,'select into/bulkcopy', 'false'
go