Complete a Server Assessment – Database Size Information

Below you will find the code to gather information about the size of each database.  Simply select the text and copy to the Clipboard.

CREATE TABLE #DBInfo1
( DatabaseName VARCHAR(128)
, DBBame VARCHAR(50)
, FileSizeMB DECIMAL(32,2)
, SpaceUsedMB DECIMAL(32,2)
, FreeSpaceMB DECIMAL(32,2))

EXECUTE master.sys.sp_MSforeachdb ‘USE [?] INSERT INTO #DBInfo1 SELECT
DB_Name() ,name
, CONVERT(DECIMAL(12,2),ROUND(a.size/128.000,2)) AS FileSizeMB
, CONVERT(DECIMAL(12,2),ROUND(FILEPROPERTY(a.name,”SpaceUsed”)/128.000,2)) AS SpaceUsedMB
, CONVERT(DECIMAL(12,2),ROUND((a.size-FILEPROPERTY(a.name,”SpaceUsed”))/128.000,2)) AS FreeSpaceMB
FROM dbo.sysfiles a ‘

SELECT ‘DB File Information – Size’ AS ‘Setting’, *

FROM #DBInfo1

DROP TABLE #DBInfo1

This code was written using the Microsoft SQL Server documentation.  If you find that this code was copied from elsewhere, please let me know so I can give proper credit.