Free Space in All Databases

Which databases have room to grow, and which are about to run out of disk? This script walks every online database on the server, records each file's size, used space and free space in gigabytes into a global temp table, and reports both a server-wide total (with and without tempdb) and a per-file breakdown sorted by free space descending.

SET NOCOUNT ON;

DECLARE @tSQL nVarChar(MAX)
DECLARE @DBName nVarChar(MAX)

CREATE TABLE ##FileSizes
(
	[database] nVarChar(128),
	name nVarChar(128),
	[type] nVarChar(128),
	[filename] nVarChar(512),
	filesizeGB decimal(12,2),
	spaceusedGB decimal(12,2),
	freespaceGB decimal(12,2)
)

DECLARE DBs CURSOR FAST_FORWARD FOR
	SELECT
		name
	FROM
		sys.databases
	WHERE
		state_desc = 'ONLINE'
OPEN DBs

FETCH FROM DBs INTO @DBName

WHILE(@@FETCH_STATUS = 0)
BEGIN--WHILE

	SET @tSQL =
		'USE [' + @DBName + ']
		INSERT INTO ##FileSizes ([database], [type], name, [filename], filesizeGB, spaceusedGB, freespaceGB)
		SELECT
			''' + @DBName + ''',
			DBF.type_desc,
			SysF.[name],
			SysF.[filename],
			convert(decimal(12,2),round(SysF.size/128.000,2)/ 1024.0) as filesizeGB,
			convert(decimal(12,2),round(fileproperty(SysF.name,''SpaceUsed'')/128.000,2)/ 1024.0) as spaceusedGB,
			convert(decimal(12,2),round((SysF.size-fileproperty(SysF.name,''SpaceUsed''))/128.000,2)/ 1024.0) as freespaceGB
		FROM
			dbo.sysfiles as SysF
		INNER JOIN sys.database_files as DBF
			ON DBF.file_id = SysF.fileid'
	EXEC (@tSQL)
	

	FETCH FROM DBs INTO @DBName
END--WHILE

CLOSE DBs DEALLOCATE DBs

SELECT
	(SELECT SUM(freespaceGB) FROM ##FileSizes) as [Total Free Space],
	(SELECT SUM(freespaceGB) FROM ##FileSizes WHERE [database] <> 'tempdb') as [Total Free Space (Excluding tempdb)]

SELECT
	[database] as [Database],
	[type] as [Type],
	[name] as [Logical File],
	[filename] as [Physical File],
	filesizeGB as [File Size (GB)],
	spaceusedGB  as [Used Space (GB)],
	freespaceGB as [Free Space (GB)]
FROM
	##FileSizes
ORDER BY
	freespaceGB DESC

DROP TABLE ##FileSizes