SQL Function: ListDrives

It seems that I am constantly needing to look at the available drives and free space on each SQL Server for one thing or another. I for one am tired of using the xp_fixeddrives extended sproc and a temp table to get started. On top of that, it doesnt give me everything I realy need to know about each drive. So I wrote a Table function that can be joined into a query an provides almost every bit of information I can get on each drive.

SELECT * FROM [dbaadmin].[dbo].[dbaudf_ListDrives] ()

Returns a Table with the following Columns:

• DriveLetter
• TotalSize Something really needed to accurately determine an accurate percent free .
• AvailableSpace
• FreeSpace
• DriveType
• SerialNumber
• FileSystem
• IsReady This is handy in a clustered environment to see which drives are part of the cluster group.
• ShareName
• VolumeName
• Path
• RootFolder

Some of these values are redundant but I wanted to include every Property of the "Scripting.FileSystemObject". Feel free to provide feedback and let me know if improvements can be made.

here is the code:

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE FUNCTION [dbo].[dbaudf_ListDrives]()

RETURNS @DriveList Table

(

[DriveLetter] CHAR(1)

,[TotalSize] BigInt

,[AvailableSpace] BigInt

,[FreeSpace] BigInt

,[DriveType] VarChar(50)

,[SerialNumber] VarChar(50)

,[FileSystem] VarChar(50)

,[IsReady] VarChar(50)

,[ShareName] VarChar(255)

,[VolumeName] VarChar(255)

,[Path] VarChar(2048)

,[RootFolder] VarChar(2048)

)

AS

BEGIN

DECLARE @DriveLoop INT

DECLARE @fso Int

DECLARE @DriveCount INT

DECLARE @Drives Int

DECLARE @Drive Int

DECLARE @Property nVarChar(100)

DECLARE @Results VarChar(8000)

DECLARE @Results_int bigint

SET @DriveLoop = 65

exec sp_OACreate 'Scripting.FileSystemObject', @fso OUT

exec sp_OAGetProperty @fso,'Drives', @Drives OUT

exec sp_OAGetProperty @Drives,'Count', @DriveCount OUT

WHILE @DriveLoop <>

BEGIN

SET @Property = 'item("'+CHAR(@DriveLoop)+'")'

--SET @Property = 'Drives.item("A").DriveLetter'

--exec sp_OAGetProperty @fso,@Property, @Results OUT

exec sp_OAGetProperty @Drives,@Property, @Drive OUT

exec sp_OAGetProperty @Drive,'DriveLetter', @Results OUT

IF @Results = CHAR(@DriveLoop)

BEGIN

INSERT INTO @DriveList ([DriveLetter]) VALUES(@Results)

exec sp_OAGetProperty @Drive,'TotalSize' , @Results_int OUT; UPDATE @DriveList SET [TotalSize] = @Results_int WHERE [DriveLetter] = CHAR(@DriveLoop)

exec sp_OAGetProperty @Drive,'AvailableSpace' , @Results_int OUT; UPDATE @DriveList SET [AvailableSpace] = @Results_int WHERE [DriveLetter] = CHAR(@DriveLoop)

exec sp_OAGetProperty @Drive,'FreeSpace' , @Results_int OUT; UPDATE @DriveList SET [FreeSpace] = @Results_int WHERE [DriveLetter] = CHAR(@DriveLoop)

exec sp_OAGetProperty @Drive,'DriveType' , @Results OUT; UPDATE @DriveList SET [DriveType] = @Results WHERE [DriveLetter] = CHAR(@DriveLoop)

exec sp_OAGetProperty @Drive,'SerialNumber' , @Results OUT; UPDATE @DriveList SET [SerialNumber] = @Results WHERE [DriveLetter] = CHAR(@DriveLoop)

exec sp_OAGetProperty @Drive,'FileSystem' , @Results OUT; UPDATE @DriveList SET [FileSystem] = @Results WHERE [DriveLetter] = CHAR(@DriveLoop)

exec sp_OAGetProperty @Drive,'IsReady' , @Results OUT; UPDATE @DriveList SET [IsReady] = @Results WHERE [DriveLetter] = CHAR(@DriveLoop)

exec sp_OAGetProperty @Drive,'ShareName' , @Results OUT; UPDATE @DriveList SET [ShareName] = @Results WHERE [DriveLetter] = CHAR(@DriveLoop)

exec sp_OAGetProperty @Drive,'VolumeName' , @Results OUT; UPDATE @DriveList SET [VolumeName] = @Results WHERE [DriveLetter] = CHAR(@DriveLoop)

exec sp_OAGetProperty @Drive,'Path' , @Results OUT; UPDATE @DriveList SET [Path] = @Results WHERE [DriveLetter] = CHAR(@DriveLoop)

exec sp_OAGetProperty @Drive,'RootFolder' , @Results OUT; UPDATE @DriveList SET [RootFolder] = @Results WHERE [DriveLetter] = CHAR(@DriveLoop)

END

SET @DriveLoop = @DriveLoop +1

END

RETURN

END

GO