운영체제 Disk 사용량 보기
sp_OAMethod와 sp_OAGetProperty에 사용되는 method들을 찾아보려 하였지만, 정리가 되어 있는것이 없는건지 저의 인터넷 검색 능력이 떨어지는것인지, 쩝
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
DBA 이승연
Microsoft Certified Trainer (MCT) SQL Server
GRAVITY co.,Ltd. Global Business Dept / Tech Support
Tel 02-2132-7351 / Fax 02-2132-7330
MOBILE 010-9138-7789
-----------------------------------------------------