Script to Generate Database Allocated Size Report

SQL Server has many nice built-in reports but I wrote this one because I couldn't find a quick reference to the current space allocations of all the databases on a server instance. Sample output is shown below.  I hope you find this script as useful as I have.

 

declare @DBSize decimal(12,2),
 @name char(35),
 @Header varchar (8000),
 @EntireLine varchar(8000),
 @DecimalValue decimal(10,2),
 @TotalSize decimal(12,2) , -- = 0,
 @TotalLine varchar(8000)
select @totalsize = 0
DECLARE Databases_Cursor Cursor
   FOR SELECT sd.name, sum(smf.size)
FROM sys.master_files smf inner join sys.databases sd on smf.database_id = sd.database_id
where sd.name not in ('Tempdb')  --and (sd.name like 'c13_CCHP_Rolling%' or sd.name like 'dbname2%')

group by sd.name, smf.database_id
order by
-- sd.name  --  *** Sort by db name
sum(smf.size) desc  -- *** Sort by db size
select @Header = 'SQL Instance: ' + @@Servername + char(13) + char(10) + char(13) + char(10) +
'Database                    Allocated Size (GB)' + 
+ char(13) + char(10) + '________________________________________________'
+ char(13) + char(10) + char(13) + char(10)
print @Header
OPEN Databases_Cursor
FETCH NEXT FROM Databases_Cursor
INTO @name, @DBSize
WHILE @@FETCH_STATUS = 0
Begin
 select @Entireline = @name + ' ' + Cast(CONVERT(DECIMAL(10,2),@DBSize * 8 / 1000000, 8) as nvarchar) + ' GB'
 select @TotalSize = @TotalSize + @DBSize
 print @Entireline
 FETCH NEXT FROM Databases_Cursor
 INTO @name, @DBSize
END
Select @TotalLine = '                                 __________ ' + char(13) + char(10) + char(13) + char(10) +
     '                          Total:  ' + Cast(CONVERT(DECIMAL(10,2),@TotalSize * 8 / 1000000, 8) as nvarchar) + ' GB'
print @TotalLine
Select @TotalLine = char(13) + char(10) + char(13) + char(10)
print @TotalLine
CLOSE Databases_Cursor
DEALLOCATE Databases_Cursor
GO