Database Size Info

declare @dbname sysname

set nocount on

if @dbname is not null and @dbname not in (select name from sysdatabases (nolock))

begin

raiserror('Incorrect database name. ', 16, 1)

end

create table #datafilestats

(    dbname varchar(25),

    flag bit default 0,

    Fileid tinyint,

    [FileGroup] tinyint,

    TotalExtents dec (8, 1),

    UsedExtents dec (8, 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

drop table #datafilestats 

drop table #sizeinfo