SQLINFO – We play with data and technology

Below is the Script to list all the database file sizes and its Free spaces.

-- ============================================= 
-- Author:    Varun Rajan 
-- Create date: 27/10/2020 
-- Description:  To Find the database File Sizes and its Free spaces in an Instance 
-- ============================================= 
DECLARE @databases NVARCHAR(max) 
DECLARE @usedatabase NVARCHAR(max) 

CREATE TABLE #databasefilesizes 
  ( 
     [name]           NVARCHAR(max), 
     [filesize]       NUMERIC(38, 5), 
     [freespace]      NUMERIC(38, 5), 
     filetype         CHAR(10), 
     physicalfilename NVARCHAR(max) 
  ) 

DECLARE cur_eachdb CURSOR FOR 
  SELECT NAME 
  FROM   sys.databases 

OPEN cur_eachdb 

FETCH next FROM cur_eachdb INTO @databases 

WHILE @@FETCH_STATUS = 0 
  BEGIN 
      SET @usedatabase = 'USE [' + @databases 
                         + ']  insert into #DatabaseFileSizes  SELECT name, size/128.0 FileSizeInMB, size/128.0 - CAST(FILEPROPERTY(name, ''SpaceUsed'') AS int)/128.0     AS EmptySpaceInMB , type_desc,physical_name FROM sys.database_files; ' 

      PRINT @usedatabase 

      EXEC Sp_executesql 
        @usedatabase; 

      SET @usedatabase = '' 

      FETCH next FROM cur_eachdb INTO @databases 
  END 

CLOSE cur_eachdb; 

DEALLOCATE cur_eachdb; 

SELECT * 
FROM   #databasefilesizes;