Quick and dirty script to ascertain datafile headroom

/*

Author: Haden Kingsland (FlyingDBA)
Date: 19th April 2017
Description: A quick script to ascertain the headroom in SQL Server data files based on
			 current size of data against the max size set for the file.
			 You WILL need to have a max size set for your data file for this to work!

			 This script is offered by the "FlyingDBA" as is, with no warranties or guarantees
			 and should be used with caution in any environment. The FlyingDBA takes no responsibility
			 for environments not under his current control and advises that all scripts are run in 
			 non production environments to verify their usefulness prior to moving to production!
*/
 
 create table #drives
	   (
	   --servername varchar(100),
	   driveletter varchar(1),
	   MBFree int
	   )

	   declare @drive varchar(100),
				@fname varchar(5)
	   set @drive = 'F'
	   set @fname = @drive + ':\%'

	print @drive
	print @fname

	   insert into #drives exec xp_fixeddrives
-- uncomment for SQL 2012 and above as you can use the new "concat" function
	   --select driveletter, concat(MBFree,' MB') as 'MB Free', concat(MBFree/1024,' GB') as 'GB Free' from #drives
-- uncomment for 2008 R2 and below, as you need to use {fn concat] instead!
	   select driveletter, {fn concat (convert(varchar(10),MBFree),' MB')} as 'MB Free', {fn concat (convert(varchar(10),MBFree/1024),' GB')} as 'GB Free' 
	   from #drives
	   where driveletter = @drive 

	   select af.name, af.filename,
       convert(bigint,af.size * 8/1024) as 'Current Size (MB)',
       convert(bigint,af.maxsize * 8 / 1024) as 'Max Size (MB)',
       convert(bigint,(af.maxsize - af.size) * 8 / 1024) as 'Headroom (MB)',
       convert(bigint,(af.maxsize - af.size) * 8 / 1024 / 1024) as 'Headroom (GB)'
       from sys.sysaltfiles af
       inner join sys.databases d
       on af.dbid = d.database_id
       where d.state_desc = 'ONLINE' -- only online databases
       and af.maxsize not in (-1,268435456) -- only check database files with a max size set!
	   and af.filename like @fname 
	   order by 6 desc

	   drop table #drives