T-SQL Query to find Drive space details

Here is a T-SQL Query to find Drive space details.
This query returns Total drive space, Used space on the drive, Free space on the drive along with the free space percentage.

This requires, enabling xp_cmdshell

Enable xp_cmdshell:

EXEC sp_configure 'show advanced options' , 1
GO
RECONFIGURE
GO

EXEC sp_configure 'xp_cmdshell' , 1
GO
RECONFIGURE
GO

Once xp_cmdshell is enabled, executing the below script will provide the details about the drives of the server that hosts the connected SQL Server Instance

 DECLARE @MOUNTVOL TABLE
 ( MOUNTVOLResult nVARCHAR(500)
  ,ExecCommand nVARCHAR(500))
 
INSERT INTO @MOUNTVOL (MOUNTVOLResult) 
EXEC XP_CMDSHELL 'MOUNTVOL'
 
DELETE @MOUNTVOL WHERE MOUNTVOLResult LIKE '%VOLUME%'
DELETE @MOUNTVOL WHERE MOUNTVOLResult IS NULL
DELETE @MOUNTVOL WHERE MOUNTVOLResult NOT LIKE '%:%'
DELETE @MOUNTVOL WHERE MOUNTVOLResult LIKE '%MOUNTVOL%'
DELETE @MOUNTVOL WHERE MOUNTVOLResult LIKE '%RECYCLE%'
 
UPDATE @MOUNTVOL SET ExecCommand = 'EXEC XP_CMDSHELL ''FSUTIL VOLUME DISKFREE ' + LTRIM(RTRIM(MOUNTVOLResult)) +''''
 
DECLARE @DRIVESpace TABLE
 ( DriveLetter VARCHAR(10)
  ,DriveInfo VARCHAR(100))
   
WHILE (SELECT COUNT(*) FROM @MOUNTVOL) <>0
BEGIN
 DECLARE @Command nVARCHAR(500), @DriveLetter nVARCHAR(10)
 Select @Command = ExecCommand, @DriveLetter= MOUNTVOLResult from @MOUNTVOL
 INSERT INTO @DRIVESpace (DriveInfo) Exec sp_executeSQL @Command
 UPDATE @DRIVESpace SET DriveLetter=@DriveLetter WHERE DriveLetter IS NULL
 DELETE FROM @MOUNTVOL WHERE ExecCommand=@Command
END
 
DECLARE @FinalResults TABLE
 ( DriveLetter nVARCHAR(10)
  ,[TotalDriveSpace(MB)] DECIMAL(18,2)
  ,[UsedSpaceOnDrive(MB)] AS ([TotalDriveSpace(MB)] - [FreeSpaceOnDrive(MB)])
  ,[FreeSpaceOnDrive(MB)] DECIMAL(18,2)
  ,[TotalDriveSpace(GB)] AS CAST(([TotalDriveSpace(MB)]/1024) AS DECIMAL(18,2))
  ,[UsedSpaceOnDrive(GB)] AS CAST((([TotalDriveSpace(MB)] - [FreeSpaceOnDrive(MB)])/1024) AS DECIMAL(18,2))
  ,[FreeSpaceOnDrive(GB)] AS CAST(([FreeSpaceOnDrive(MB)]/1024) AS DECIMAL(18,2))
  ,[%FreeSpace] AS CAST((([FreeSpaceOnDrive(MB)]/[TotalDriveSpace(MB)])*100) AS DECIMAL(18,2)))
 
INSERT INTO @FinalResults (DriveLetter, [TotalDriveSpace(MB)],[FreeSpaceOnDrive(MB)])
SELECT RTRIM(LTRIM(DriveLetter))
    ,[TotalDriveSpace(MB)] = SUM(CASE WHEN DriveInfo LIKE 'TOTAL # OF BYTES%' THEN CAST(SUBSTRING(DriveInfo, 32, 48) AS FLOAT) ELSE CAST(0 AS FLOAT) END)/1024/1024
    ,[FreeSpaceOnDrive(MB)] = SUM(CASE WHEN DriveInfo LIKE 'TOTAL # OF FREE BYTES%' THEN CAST(SUBSTRING(DriveInfo, 32, 48) AS FLOAT) ELSE CAST(0 AS FLOAT) END)/1024/1024
FROM @DRIVESpace
WHERE DriveInfo LIKE 'TOTAL # OF %'
GROUP BY DriveLetter
ORDER BY DriveLetter
 
SELECT * FROM @FinalResults