디스크공간 / 여유공간

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

이런 좋은 쿼리가 있네요... 출처는 주석에~