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