sp_OAMethod 를 이용하여 디스크용량보는 T-SQL입니다.

제가 만든건 아니고 블로그에서 펌 한거에요 *^^*

use master

go

if object_id('dbo.usp_fixeddrives') is not null

    drop procedure dbo.usp_fixeddrives

go 

create procedure dbo.usp_fixeddrives

as

/*

*********************************************************************

Author :      Bouarroudj Mohamed

E-mail :      mbouarroudj@sqldbtools.com

Date   :      March 2005

Description : xp_fixeddrives wrapper

Note : you can add handling error as following (see BOL):

    exec @hr = sp_OAMethod @ObjectToken,'GetDrive', @odrive OUT, @drive 

    if @hr <> 0

    begin

        -- Obtains OLE Automation error information

        exec sp_OAGetErrorInfo ...

        handle error

    end

*********************************************************************

*/

set nocount on

---------------------------------------------------------------------

-- Declarations

---------------------------------------------------------------------

declare

    @ObjectToken     int,

    @drive           char(1),

    @odrive          int,

    @TotalSize       varchar(20),

    @MB              bigint

---------------------------------------------------------------------

-- Initializations

---------------------------------------------------------------------

set @MB = 1048576

create table #Driveslist

(

    Drive         char(1) NOT NULL,

    FreeSpaceMB   int NULL,

    TotalSizeMB   int NULL

)

---------------------------------------------------------------------

-- Processing

---------------------------------------------------------------------

insert #Driveslist(Drive, FreeSpaceMB)

exec master.dbo.xp_fixeddrives

exec sp_OACreate 'Scripting.FileSystemObject', @ObjectToken OUT  --Creates an instance of the OLE object on an instance of SQL Server.

declare DriveslistCur cursor local fast_forward

for

select Drive from #Driveslist

open DriveslistCur

fetch next from DriveslistCur into @drive

while @@FETCH_STATUS = 0

begin

    -- Calls a method GetDrive

    exec sp_OAMethod @ObjectToken, 'GetDrive', @odrive OUT, @drive 

    -- Gets a property TotalSize

    exec sp_OAGetProperty @odrive,'TotalSize', @TotalSize OUT

    update #Driveslist

    set TotalSizeMB = @TotalSize / @MB

    where drive = @drive

    fetch next from DriveslistCur into @drive

end

close DriveslistCur

deallocate DriveslistCur

exec sp_OADestroy @ObjectToken

select

    drive,

    TotalSizeMB as 'Total(MB)',

    FreeSpaceMB as 'Free(MB)',

    CAST(TotalSizeMB/1024.0 as decimal(10,2)) 'Total(GB)',

    CAST(FreeSpaceMB/1024.0 as decimal(10,2))'Free(GB)',

    CAST((FreeSpaceMB/(TotalSizeMB * 1.0)) * 100.0 as int) as 'Free(%)'

from #Driveslist

order by drive

drop table #Driveslist

return

go

exec dbo.xp_fixeddrives

go

exec dbo.usp_fixeddrives

go