Sunday, April 03, 2011

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