Display Database, File, Free and Total Disk Space

/******************************************************************
*
* 		SQL Server Disk Space Check
* 
* This script displays database and file size, compared to total
* and free disk space. It also displays the number of Kb that each
* file will grow by on the next extend.
*
* The script displays sizes in Mb and Kb, so there are sometimes
* small rounding errors in the result set.
*
* Required Permissions:
*	CREATE TABLE (in current db)
*	EXEC ON master.dbo.xp_fixeddrives
*	EXEC ON master.dbo.sp_databases
*	EXEC ON sp_executesql
*	EXEC ON sp_helpfile (in all db's)
*	EXEC ON master.dbo.sp_OACreate
*	EXEC ON master.dbo.sp_OAMethod
*	EXEC ON master.dbo.sp_OADestroy
*	SELECT ON master.dbo.sysaltfiles
*	SELECT ON master.dbo.sysdatabases
*	
******************************************************************/

BEGIN

	/*****************************************
	* Create temp tables for disk space info
	*****************************************/
	CREATE TABLE #space (dletter varchar(2), fspace int, tspace BIGINT)
	CREATE TABLE #dbsize (dbname varchar(50), dbsize int, remarks varchar(255))
	CREATE TABLE #fdata ([name] VARCHAR(255), [filename] VARCHAR(255), 
				[filegroup] VARCHAR(10), [size] VARCHAR(50),
				[maxsize] VARCHAR(50), growth VARCHAR(20), usage VARCHAR(20))
	CREATE TABLE #growth (dbname VARCHAR(50), fname VARCHAR(255), next_exp INT, gtype VARCHAR(2))

	/*****************************************
	* populate temp tables
	*****************************************/
	INSERT INTO #space (dletter, fspace) EXEC master.dbo.xp_fixeddrives
	INSERT INTO #dbsize EXEC master.dbo.sp_databases
	
	-- Create cursor for files
	DECLARE c_files CURSOR FOR
		SELECT RTRIM(af.fileid), RTRIM(af.[name]), RTRIM(af.[filename]), 
			RTRIM(af.[size]), RTRIM(db.[name])
		FROM master.dbo.sysaltfiles af, master.dbo.sysdatabases db
		WHERE af.dbid = db.dbid AND db.version <> 0
	DECLARE @tfileid INT, @tname VARCHAR(255), @tfilename VARCHAR(255)
	DECLARE @tsize INT, @tdbname VARCHAR(50)
	DECLARE @SQL NVARCHAR(255)
	DECLARE @growth VARCHAR(20), @next_exp INT, @gtype VARCHAR(2)

	-- Open cursor
	OPEN c_files
	FETCH NEXT FROM c_files
	INTO @tfileid, @tname, @tfilename, @tsize, @tdbname

	-- Populate #growth table with file growth details
	WHILE @@fetch_status = 0
	BEGIN
		TRUNCATE TABLE #fdata
		-- Get file data
		SET @SQL = 'INSERT INTO #fdata EXEC '
		SET @SQL = @SQL + @tdbname + '.dbo.sp_helpfile ''' + @tname + ''''
		EXEC sp_executesql @SQL
		SELECT @growth = growth FROM #fdata
		-- Determine if growth is % or Mbytes
		IF RIGHT(@growth,1) = '%'
		BEGIN
			SET @next_exp = CAST(LEFT(@growth, LEN(@growth) - 1) AS INT)
			SET @next_exp = CAST(ROUND(((CAST(@tsize AS FLOAT) * 8) / 100) * @next_exp,0) AS INT)
			SET @gtype = '%'
		END
		ELSE
		BEGIN
			SET @next_exp = CAST(LEFT(@growth, CHARINDEX(' ',@growth)) AS INT)
			SET @gtype = 'MB'
		END
		-- Create record for file in #growth table
		INSERT INTO #growth VALUES (@tdbname, @tname, @next_exp, @gtype)
		FETCH NEXT FROM c_files
		INTO @tfileid, @tname, @tfilename, @tsize, @tdbname
	END

	-- Close cursor
	CLOSE c_files
	DEALLOCATE c_files

	/*****************************************
	* Update temp table info with total disk sizes
	*****************************************/
	-- Create cursor for disk space table
	DECLARE c_disks CURSOR FOR
		SELECT dletter, fspace, tspace FROM #space
		FOR UPDATE
	DECLARE @dletter VARCHAR(2), @fspace INT, @tspace BIGINT

	-- Create FileSystemObject
	DECLARE @oFSO INT, @oDrive INT, @drsize VARCHAR(255), @ret INT
	EXEC @ret = master.dbo.sp_OACreate 'scripting.FileSystemObject', @oFSO OUT
	
	-- Open cursor and fetch first row
	OPEN c_disks
	FETCH NEXT FROM c_disks
	INTO @dletter, @fspace, @tspace

	-- Loop through all records in the cursor
	WHILE @@fetch_status = 0
	BEGIN
		-- Get disk size
		SET @dletter = @dletter + ':\'
		EXEC @ret = master.dbo.sp_OAMethod @oFSO, 'GetDrive', @oDrive OUT, @dletter
		EXEC @ret = master.dbo.sp_OAMethod @oDrive, 'TotalSize', @drsize OUT
		-- Update table
		UPDATE #space
		SET tspace = CAST(@drsize AS BIGINT)
		WHERE CURRENT OF c_disks
		-- Destory oDrive
		EXEC master.dbo.sp_OADestroy @oDrive
		-- Fetch next row
		FETCH NEXT FROM c_disks
		INTO @dletter, @fspace, @tspace
	END

	-- Close cursor
	CLOSE c_disks
	DEALLOCATE c_disks

	-- Destroy FSO
	EXEC master.dbo.sp_OADestroy @oFSO

	/*****************************************
	* Return disk space info
	*****************************************/
	SELECT db.[name] as [Database Name], 
		af.[name] as [File Name],
		CAST(ROUND(CAST(ds.dbsize AS FLOAT) / 1024,0) AS INT) as [Database Size (Mb)],  
		CAST(ROUND((CAST(af.[size] AS FLOAT) * 8) / 1024,0) AS INT) as [File Size (Mb)], 
		s.fspace as [Free Disk Space (Mb)], 
		CAST(ROUND((CAST(s.tspace AS FLOAT) / 1024) /1024,0) AS INT) as [Total Disk Space (Mb)],
		STR(g.next_exp) + ' KB' as [Next Extension],
		--CAST(ROUND(CAST(g.next_exp AS FLOAT) / 1024,0) AS INT) as [Next Expansion],
		af.[filename] as [File Path]
	FROM master.dbo.sysaltfiles af, #space s, #dbsize ds, 
		master.dbo.sysdatabases db, #growth g
	WHERE s.dletter = LEFT(af.[filename],1)
	AND af.dbid = db.dbid
	AND db.[name] = ds.dbname
	AND g.dbname = db.[name]
	AND g.fname = af.[name]
	ORDER BY db.[name], af.[name]

	/*****************************************
	* Drop temporary tables
	*****************************************/
	DROP TABLE #space
	DROP TABLE #dbsize
	DROP TABLE #fdata
	DROP TABLE #growth

END