Hi !
with the following altered version of sp_helpdb you can generate db name and db size report .. then you can further alter this sp to generate reports as you want ..
-----------------
Code:
create procedure sp_getdb
as
declare @exec_stmt nvarchar(625)
declare @name sysname
declare @low nvarchar(11)
set nocount on
create table #spdbdesc
(
dbname sysname,
dbsize nvarchar(13) null,
dbid smallint
)
if
select @low = convert(varchar(11),low) from master.dbo.spt_values
where type = 'E' and number = 1
--create the table
insert into #spdbdesc (dbname,dbid)
select name,dbid from master.dbo.sysdatabases
--set the collation
select @low = convert(varchar(11),low) from master.dbo.spt_values
where type = 'E' and number = 1
declare ms_crs_c1 cursor for
select db_name (dbid) from #spdbdesc
open ms_crs_c1
fetch ms_crs_c1 into @name
while @@fetch_status >= 0
begin
--Insert row for each database
select @exec_stmt = 'update #spdbdesc set dbsize =
(select str(convert(dec(15),sum(size))* ' + @low + '/ 1048576,10,2)+ '' MB'' from '+ quotename(@name, '[') + '.dbo.sysfiles) WHERE current of ms_crs_c1'
execute (@exec_stmt)
fetch ms_crs_c1 into @name
end
deallocate ms_crs_c1
select dbid,
name = dbname,
db_size = dbsize
from #spdbdesc
order by dbid
return (0)
if you are not able to tailor made it like your descrips please tell me i will do that for you ..
>>>>>>>>>>>>>>>>
I have known a great many troubles, but most of them never happened. -- Mark Twain
Collect database space used and generate a report
