SQL Script to show available space for all Databases

22 May

Quick little script to return the space available on all databases. I linked this into PRTG Network Monitor to make sure that none of them were too low.

--////////////////////// Filespace Used and Free   //////////////////////////////////////////
IF (OBJECT_ID('tempdb..#tmpDbSpace') IS NOT NULL)
      DROP TABLE #tmpDbSpace

CREATE TABLE #tmpDbSpace (
      [DBname] varchar (126) NULL, 
      [LogicalFileName] varchar (126) NULL,
      [IsLogFile] tinyint NULL,
      [IsPrimaryFile] tinyint NULL,
      [IsReadOnly] tinyint NULL,
      [FileSizeMB] numeric (12, 2) NULL,
      [SpaceUsedMB] numeric (12, 2) NULL,
      [Growth] varchar (50) NULL)

DECLARE @TSQL varchar (max)

SET @TSQL = 'USE [?]; INSERT INTO #tmpDbSpace
                              [IsLogFile]=FILEPROPERTY([name], ''IsLogFile''),
                              [IsPrimaryFile]=FILEPROPERTY([name], ''IsPrimaryFile''),
                              [IsReadOnly]=FILEPROPERTY([name], ''IsReadOnly''),
                              [FileSizeMB]=([Size]*8)/1024, -- In pages of 8 Kb
                              [SpaceUsedMB]=(FILEPROPERTY([name], ''SpaceUsed'')*8)/1024, -- In pages of 8 Kb
                              [Growth] = CASE status & 0x100000
                              WHEN 0x100000 THEN      CONVERT(VARCHAR(15), growth) + ''%''
                              ELSE CONVERT(VARCHAR(15), CONVERT (BIGINT, growth) * 8) + '' KB'' END
                              FROM sys.sysfiles'

--Print the command to be issued against all databases  

--Run the command against each database  
EXEC sp_MSforeachdb @TSQL 

WHEN [IsLogFile]=1 THEN 'Log'
WHEN [IsLogFile]=0 AND [IsPrimaryFile]=1 THEN 'Primary'
ELSE 'Data'
WHEN [FileSizeMB] = 0 THEN 0
ELSE CAST((([FileSizeMB]-[SpaceUsedMB])/[FileSizeMB])*100 AS numeric (12, 2))
FROM #tmpDbSpace
ORDER BY [DBname], [IsLogFile]