SQL Server: TSQL Script to Get Hard Drives Detail

Recently, I need a script which can provide me detail (Driver Letter, Drive Label, Free Space, Used Space etc) of all installed hard drives. I found following script by G. Rayburn very helpful.

Valid for versions: SQL Server 2005 and above

Note: Before executing script, don't forget to enable Ole Automation Procedures from Surface Area Configuration or using sp_configure.

/********************************************************

**    Author:  G. Rayburn

*********************************************************/

SET NOCOUNT ON

IF EXISTS (SELECT name FROM tempdb..sysobjects WHERE name = '##_DriveSpace')

      DROP TABLE ##_DriveSpace

IF EXISTS (SELECT name FROM tempdb..sysobjects WHERE name = '##_DriveInfo')

      DROP TABLE ##_DriveInfo

DECLARE @Result INT

      , @objFSO INT

      , @Drv INT

      , @cDrive VARCHAR(13)

      , @Size VARCHAR(50)

      , @Free VARCHAR(50)

      , @Label varchar(10)

CREATE TABLE ##_DriveSpace

      (

        DriveLetter CHAR(1) not null

      , FreeSpace VARCHAR(10) not null

       )

CREATE TABLE ##_DriveInfo

      (

      DriveLetter CHAR(1)

      , TotalSpace bigint

      , FreeSpace bigint

      , Label varchar(10)

      )

INSERT INTO ##_DriveSpace

      EXEC master.dbo.xp_fixeddrives

-- Iterate through drive letters.

DECLARE  curDriveLetters CURSOR

      FOR SELECT driveletter FROM ##_DriveSpace

Read more: Connect SQL

Posted via email from Jasper-net