Google Custom Search

Tuesday, April 21, 2009

Drive Details using TSQL

SET NOCOUNT ON

DECLARE @res INT, 
@objdets INT,
@drive INT, 
@getdrive VARCHAR(13), 
@totalsize VARCHAR(50), 
@freespace VARCHAR(50), 
@DriveLetter char(1)

CREATE TABLE ##Drivespacedetails
(
DriveLetter CHAR(1),
TotalSpace bigint,
FreeSpace bigint,
)

INSERT INTO ##Drivespacedetails(DriveLetter,FreeSpace) EXEC master.dbo.xp_fixeddrives

DECLARE  drivedetails CURSOR FOR SELECT DriveLetter FROM ##Drivespacedetails

OPEN drivedetails

FETCH NEXT FROM drivedetails INTO @DriveLetter
WHILE (@@fetch_status <> -1)
BEGIN
SET @getdrive = 'GetDrive("' + @DriveLetter + '")' 
         EXEC @res = sp_OACreate 'Scripting.FileSystemObject', @objdets OUTPUT 
         IF @res = 0  
         EXEC @res = sp_OAMethod @objdets, @getdrive, @drive OUTPUT 
         IF @res = 0  
         EXEC @res = sp_OAGetProperty @drive,'TotalSize', @totalsize OUTPUT 
    IF @res = 0  
         EXEC @res = sp_OAGetProperty @drive,'FreeSpace', @freespace OUTPUT 
         IF @res <> 0  
         EXEC sp_OADestroy @drive 
EXEC sp_OADestroy @objdets 
SET @totalsize = (CONVERT(BIGINT,@totalsize) / 1048576 )
SET @freespace = (CONVERT(BIGINT,@freespace) / 1048576 )

     update ##Drivespacedetails set TotalSpace=@totalsize,FreeSpace=@freespace where DriveLetter=@DriveLetter
FETCH NEXT FROM drivedetails INTO @DriveLetter
END

CLOSE drivedetails
DEALLOCATE drivedetails

SELECT @@servername as ServerName,DriveLetter,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 ##Drivespacedetails ORDER BY [DriveLetter] ASC

DROP TABLE ##Drivespacedetails

2 comments:

Anonymous said...

Hi man thanks for yoir blog is very interesting.

I have a question for you, hope you can help

the sql server 2000 64 bits is available for all processors 64 bits? xeon for example

SQL Server 2000 64-bit software is available only for 64-bit IA opeating? what is IA?.

call microsoft, but tell me that this version is not available, you know of any places to get?

you are eternally grateful for the information, thanks

Welsi Tuesta from Peru.
wtuesta@iticsa.com

Aashish said...

Also to run this TSql correctly you should have OLE Automation enabled.If Ole automation is not enabled this TSql will result into errors