Database Count, Allocated and Used Space

A quick capacity snapshot of the whole server: this script walks every online database, records each file's total size, used space and free space in gigabytes into a global temp table, and then reports the totals - how many user databases there are, how much disk is allocated, and how much of that is actually used. The system databases (model, master, msdb, tempdb) are excluded from the summary.

SET NOCOUNT ON;

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

IF object_id('tempdb..##FileSizes') IS NOT NULL
BEGIN--IF
	DROP TABLE ##FileSizes
END--IF

CREATE TABLE ##FileSizes
(
	[database] nVarChar(128),
	name 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 = 0
OPEN DBs

FETCH FROM DBs INTO @DBName

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

	SET @tSQL =
		'USE [' + @DBName + ']
		INSERT INTO ##FileSizes ([database], name, [filename], filesizeGB, spaceusedGB, freespaceGB)
		SELECT
			''' + @DBName + ''',
			[name],
			[filename],
			convert(decimal(12,2),round(a.size/128.000,2)/ 1024.0) as filesizeGB,
			convert(decimal(12,2),round(fileproperty(a.name,''SpaceUsed'')/128.000,2)/ 1024.0) as spaceusedGB,
			convert(decimal(12,2),round((a.size-fileproperty(a.name,''SpaceUsed''))/128.000,2)/ 1024.0) as freespaceGB
		from
			dbo.sysfiles as a'

	EXEC (@tSQL)

	FETCH FROM DBs INTO @DBName
END--WHILE

CLOSE DBs DEALLOCATE DBs

SELECT
	(SELECT COUNT(DISTINCT [database]) FROM ##FileSizes WHERE [database] not in ('model', 'master','msdb','tempdb')) as [Databases],
	(SELECT SUM(filesizeGB) FROM ##FileSizes WHERE [database] not in ('model', 'master','msdb','tempdb')) as [Allocated GB],
	(SELECT SUM(spaceusedGB) FROM ##FileSizes WHERE [database] not in ('model', 'master','msdb','tempdb')) as [Used GB]

--select * from ##FileSizes

DROP TABLE ##FileSizes