Problem

I was trying to get disk information like total space and free using t-sql but extended stored procedure xp_fixeddrives provides only free space info(see screen shot below).

Solution

Powershell can fetch disk information very easily. T-SQL script below uses powershell to get this information.

Pre-requisite:  xp_cmdshell  and Powershell 1.0

To enable xp_cmdshell  execute the following script.

sp_configure 'show advanced options', 1

Go 

Reconfigure 

Go 

sp_configure 'xp_cmdshell',1 

go 

Reconfigure 

Go

 

Create new store procedure sp_spaceinfo by executing following script.

USE [master]

GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_spaceinfo]') AND type in (N'P', N'PC'))

DROP PROCEDURE [dbo].[sp_spaceinfo]

GO

CREATE PROCEDURE [dbo].[sp_spaceinfo] 

AS

DECLARE @psinfo TABLE(data  NVARCHAR(100)) ;

INSERT INTO @psinfo

EXEC xp_cmdshell 'Powershell.exe "Get-WMIObject Win32_LogicalDisk -filter "DriveType=3"| Format-Table DeviceID, FreeSpace, Size"'  ;

DELETE FROM @psinfo WHERE data is null  or data like '%DeviceID%' or data like '%----%';

update @psinfo set data = REPLACE(data,' ',',');


;With DriveSpace as (

select SUBSTRING(data,1,2)  as [Drive], replace((left((substring(data,(patindex('%[0-9]%',data)) , len(data))),CHARINDEX(',',

(substring(data,(patindex('%[0-9]%',data)) , len(data))))-1)),',','')

as [FreeSpace] replace(right((substring(data,(patindex('%[0-9]%',data)) , len(data))),PATINDEX('%,%', 

(substring(data,(patindex('%[0-9]%',data)) , len(data))))) ,',','')

as [Size] from @psinfo

SELECT Drive, convert(dec( 6,2),CONVERT(dec(17,2),FreeSpace)/(1024*1024*1024)) as FreeSpaceGB, convert(dec( 6,2),CONVERT(dec(17,2), size)/(1024*1024*1024)) as SizeGB FROM DriveSpace; 

 GO

Exec sp_spaceinfo;

Go

OS output

sp_spaceinfo output

Tested On

I have tested this code on SQL Server 2008 R2 , SQL Server 2008 and SQL Server 2005.

Leave you feedback for encouragement.