SQL Query to get the drive details of a system

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

SQL Query to get the drive details of a system

Author

Topic

Lana George
Starting Member

4 Posts

Posted - 2011-03-31 : 03:16:14

Hi,
I need to write a SQL Query to fetch the total count of drives in the system,the list of all the drives present,the drive name,volume name,and the drive type of all the drives present in the system. Please guide me on this.

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2011-03-31 : 04:29:25

EXEC master..xp_fixeddrives

--------------------------
http://connectsql.blogspot.com/

Lana George
Starting Member

4 Posts

Posted - 2011-03-31 : 04:36:03

Hi,
I tried using this SP.But it returns only the free space of the drive.To get other details like drive name and volume name of all the drives is there any other stored procedure

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2011-03-31 : 05:41:11

Following well written code may help you, but please don't forget to turn on option for 'Ole Automation Procedures' from Surface Area Configuration

/******************************************************************************
**
**
** Author: G. Rayburn
**
** Date: 11/01/2005
**
** ToDo:
**
**
*******************************************************************************
** Modification History
*******************************************************************************
**
** Initial Creation: 11/01/2005 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

DECLARE @DriveLetter char(1)
OPEN curDriveLetters

FETCH NEXT FROM curDriveLetters INTO @DriveLetter
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN

SET @cDrive = 'GetDrive("' + @DriveLetter + '")'

EXEC @Result = sp_OACreate 'Scripting.FileSystemObject', @objFSO OUTPUT

IF @Result = 0

EXEC @Result = sp_OAMethod @objFSO, @cDrive, @Drv OUTPUT

IF @Result = 0

EXEC @Result = sp_OAGetProperty @Drv,'TotalSize', @Size OUTPUT

IF @Result = 0

EXEC @Result = sp_OAGetProperty @Drv,'FreeSpace', @Free OUTPUT

IF @Result = 0

EXEC @Result = sp_OAGetProperty @Drv,'VolumeName', @Label OUTPUT

IF @Result <> 0

EXEC sp_OADestroy @Drv
EXEC sp_OADestroy @objFSO

SET @Size = (CONVERT(BIGINT,@Size) / 1048576 )

SET @Free = (CONVERT(BIGINT,@Free) / 1048576 )

INSERT INTO ##_DriveInfo
VALUES (@DriveLetter, @Size, @Free, @Label)

END
FETCH NEXT FROM curDriveLetters INTO @DriveLetter
END

CLOSE curDriveLetters
DEALLOCATE curDriveLetters

PRINT 'Drive information for server ' + @@SERVERNAME + '.'
PRINT ''

-- Produce report.
SELECT DriveLetter
, Label
, FreeSpace AS [FreeSpace MB]
, (TotalSpace - FreeSpace) AS [UsedSpace MB]
, TotalSpace AS [TotalSpace MB]
, ((CONVERT(NUMERIC(9,0),FreeSpace) / CONVERT(NUMERIC(9,0),TotalSpace)) * 100) AS [Percentage Free]

FROM ##_DriveInfo
ORDER BY [DriveLetter] ASC
GO

DROP TABLE ##_DriveSpace
DROP TABLE ##_DriveInfo

--------------------------
http://connectsql.blogspot.com/

Lana George
Starting Member

4 Posts

Posted - 2011-04-01 : 09:28:54

Thanks for the code.
This worked.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources