sp_dbsize

use master
go

if exists (select name from sysobjects (nolock) where name = 'sp_CHI_dbsize' and type = 'P' )
  drop proc dbo.sp_CHI_dbsize
go

create proc dbo.sp_CHI_dbsize @dbname sysname = null
as

/*  Date: 11/18/2002
**  Author: Richard Ding
**  Important change: Use "DBCC SHOWFILESTATS" that can report real time figure on data file.
**  Purpose: Equivalent to Taskpad view. It reports realtime figures of db size info. No need 
**  to run "DBCC UPDATEUSAGE". It shows the following figures: 
**    1. Total database size in MB.
**    2. Data and log sizes in MB.
**    3. Data being used in MB and percentage.
**    4. Data free in MB and percentage.
**    5. Log being used in MB and percentage.
**    6. Log free in MB and percentage.
**  Test code:
**    exec sp_CHI_dbsize 
**    exec sp_CHI_dbsize pubs
**    exec sp_CHI_dbsize bogus     --  bogus is not a valid database
*/
set nocount on
if @dbname is not null and @dbname not in (select name from sysdatabases (nolock))
  begin
    raiserror('Incorrect database name. ', 16, 1)
    return (1)
  end
create table #datafilestats 
(dbname varchar(25), 
flag bit default 0, 
Fileid tinyint, 
[FileGroup] tinyint, 
TotalExtents dec (7, 1), 
UsedExtents dec (7, 1), 
[Name] varchar(50), 
[FileName] sysname )

declare @string sysname, @dbname1 sysname
set @string = ''
if @dbname is not null
set @dbname1 = @dbname
else 
set @dbname1 = ''

  set @dbname = ''

while 1=1
begin
  select top 1 @dbname = name from master..sysdatabases where name > @dbname --order by name asc  
  if @@rowcount = 0
    break
  set @string = 'use ' + @dbname + ' DBCC SHOWFILESTATS with no_infomsgs'
insert into #datafilestats (Fileid, [FileGroup] , TotalExtents , UsedExtents , [Name] , [FileName]) exec (@string)
  update #datafilestats set dbname = @dbname, flag = 1 where flag = 0 
update #datafilestats set TotalExtents = (select sum(TotalExtents)*8*8192.0/1048576.0 from #datafilestats where dbname = @dbname)
where flag = 1 and Fileid = 1 and FileGroup = 1 and dbname = @dbname
update #datafilestats set UsedExtents = (select sum(UsedExtents)*8*8192.0/1048576.0 from #datafilestats where dbname = @dbname)
where flag = 1 and Fileid = 1 and FileGroup = 1 and dbname = @dbname
end

create table #sizeinfo 
( db_name varchar(30) not null primary key clustered, 
  total dec (7, 1),
  data dec (7, 1), 
  data_used dec (7, 1),
  [data (%)] dec (7, 1),
  data_free dec (7, 1), 
  [data_free (%)] dec (7, 1),
  log dec (7, 1), 
  log_used dec (7, 1), 
  [log (%)] dec (7, 1),
  log_free dec (7, 1),
  [log_free (%)] dec (7, 1),
  status dec (7, 1) )
insert #sizeinfo (db_name, log, [log (%)], status ) exec ('dbcc sqlperf(logspace) with no_infomsgs')
update #sizeinfo set 
  data = d.TotalExtents from #datafilestats d join #sizeinfo s on d.dbname = s.db_name where d.flag = 1 and d.Fileid = 1 and d.FileGroup = 1 
update #sizeinfo set
  data_used = d.UsedExtents from #datafilestats d join #sizeinfo s on d.dbname = s.db_name where d.flag = 1 and d.Fileid = 1 and d.FileGroup = 1 
update #sizeinfo set
  total = (data + log)
update #sizeinfo set
[data (%)] = (data_used * 100.0 / data)
update #sizeinfo set
 data_free = (data - data_used)
update #sizeinfo set
 [data_free (%)] = (100 - [data (%)])
update #sizeinfo set
log_used = (log * [log (%)] / 100.0)
update #sizeinfo set
log_free = (log - log_used)
update #sizeinfo set
[log_free (%)] = (log_free * 100.0 / log)
print ''
if @dbname1 = ''
begin
  print 'Database size report on ' + @@servername + ' as of ' + convert(varchar(30), getdate(), 100) + char(10)
select db_name, 
         total, 
         data, 
         data_used, 
         [data (%)], 
         data_free, 
         [data_free (%)], 
         log, 
         log_used, 
         [log (%)], 
         log_free, 
         [log_free (%)] 
    from #sizeinfo order by db_name asc compute sum(total)
end
else 
begin
print 'Database size report on ' + @@servername + '.' + @dbname1 + ' as of ' + convert(varchar(30), getdate(), 100) + char(10)
select db_name, 
         total, 
         data, 
         data_used, 
         [data (%)], 
         data_free, 
         [data_free (%)], 
         log, 
         log_used, 
         [log (%)], 
         log_free, 
         [log_free (%)] 
    from #sizeinfo where db_name = @dbname1
end
go