學海無邊，書囊無底

利用SQL Server 提供的系統預存程序 xp_fixeddrives 計算硬碟使用量

DECLARE @drive CHAR(1)
DECLARE @hr INT
DECLARE @fso INT
DECLARE @odrive INT
DECLARE @TotalSize VARCHAR(20)
DECLARE @MB BIGINT ; SET @MB = 1048576

    IF object_id('dbo.Tmp_SpaceUsed_disk') IS NOT NULL  
    BEGIN  
         DROP TABLE dbo.Tmp_SpaceUsed_disk
    END 
    IF object_id('tempdb..#TBLdrive') IS NOT NULL  
    BEGIN  
         DROP TABLE #TBLdrive
    END 
    CREATE TABLE #TBLdrive (
    ServerName nvarchar(15),
    DiskName char(1) PRIMARY KEY,
    DiskSize int NULL,
    FreeSpace int NULL,

    LogDate DATETIME NULL
    )
INSERT #TBLdrive(DiskName,FreeSpace)
EXEC master.dbo.xp_fixeddrives


-- This is a VB method
EXEC sp_configure 'show advanced options',1
RECONFIGURE
EXEC sp_configure 'Ole Automation Procedures',1
RECONFIGURE
EXEC @hr=sp_OACreate 'Scripting.FileSystemObject',@fso OUT
IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso


DECLARE dcur CURSOR LOCAL FAST_FORWARD
FOR 
SELECT DiskName from #TBLdrive ORDER by DiskName
OPEN dcur
FETCH NEXT FROM dcur INTO @drive
WHILE @@FETCH_STATUS=0
    BEGIN
    PRINT @odrive
    PRINT @drive
    EXEC @hr = sp_OAMethod @fso,'GetDrive', @odrive OUT, @drive
    IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso
    EXEC @hr = sp_OAGetProperty @odrive,'TotalSize', @TotalSize OUT
    IF @hr <> 0 EXEC sp_OAGetErrorInfo @odrive
    UPDATE #TBLdrive
    SET DiskSize=@TotalSize/@MB, ServerName = replace( @@ServerName , '.master.dbo.xp_fixeddrives',''), LogDate = (GETDATE())
    WHERE DiskName=@drive
    FETCH NEXT FROM dcur INTO @drive
    END
CLOSE dcur
DEALLOCATE dcur

SELECT ServerName,Rtrim(DiskName)+':' AS DiskName,DiskSize,FreeSpace,LogDate INTO Tmp_SpaceUsed_disk
FROM #TBLdrive
EXEC sp_configure 'Ole Automation Procedures',0
RECONFIGURE
EXEC sp_configure 'show advanced options',0
RECONFIGURE

SELECT * FROM  #TBLdrive