USE [master]
GO
CREATE FUNCTION [dbo].[ufn_LogicalDiskDrives]()
RETURNS @DriveList Table
(
[DriveLetter] CHAR(1)
,[VolumeName] VARCHAR(255)
,[FileSystem] VARCHAR(50)
,[TotalSize] BIGINT
,[AvailableSpace] BIGINT
,[FreeSpace] BIGINT
)
AS
BEGIN
--Written by Percy Reyes
DECLARE @DriveLetter_ASCII_Code INT
DECLARE @FileSystemInstance INT
DECLARE @DriveCount INT
DECLARE @DriveCollection INT
DECLARE @Drive INT
DECLARE @Property NVARCHAR(100)
DECLARE @DriveLetter VARCHAR(1)
DECLARE @TotalSize BIGINT
DECLARE @AvailableSpace BIGINT
DECLARE @FreeSpace BIGINT
DECLARE @FileSystem VARCHAR(128)
DECLARE @VolumeName VARCHAR(128)
DECLARE @IsReady VARCHAR(5)
--Creating a File System Object for getting files or disk info.
exec sp_OACreate 'Scripting.FileSystemObject', @FileSystemInstance OUT
--Getting the collection of drives
exec sp_OAGetProperty @FileSystemInstance,'Drives', @DriveCollection OUT
--Getting the count of drives from collection
exec sp_OAGetProperty @DriveCollection,'Count', @DriveCount OUT
--starting from Drive "A" (ASCII 65)
SET @DriveLetter_ASCII_Code = 65
--to "Z" (ASCII 90)
WHILE @DriveLetter_ASCII_Code <= 90
BEGIN
---Creating the instance drive from Drive Collection
SET @Property = 'item("'+CHAR(@DriveLetter_ASCII_Code)+'")'
exec sp_OAGetProperty @DriveCollection,@Property, @Drive OUT
-- Getting the drive letter property
exec sp_OAGetProperty @Drive,'DriveLetter', @DriveLetter OUT
IF @DriveLetter = CHAR(@DriveLetter_ASCII_Code)
BEGIN
-- Getting more properties from each drive
exec sp_OAGetProperty @Drive,'VolumeName', @VolumeName OUT
exec sp_OAGetProperty @Drive,'FileSystem', @FileSystem OUT
exec sp_OAGetProperty @Drive,'TotalSize', @TotalSize OUT
exec sp_OAGetProperty @Drive,'AvailableSpace', @AvailableSpace OUT
exec sp_OAGetProperty @Drive,'FreeSpace', @FreeSpace OUT
exec sp_OAGetProperty @Drive,'IsReady' , @IsReady OUT;
IF @IsReady='True'
INSERT INTO @DriveList ( [DriveLetter],[TotalSize], [AvailableSpace],[FreeSpace],[FileSystem] ,[VolumeName] )
VALUES( @DriveLetter,@TotalSize,@AvailableSpace,@FreeSpace,@FileSystem,@VolumeName)
END
-- forward next drive
SET @DriveLetter_ASCII_Code = @DriveLetter_ASCII_Code +1
END
EXEC sp_OADestroy @Drive
EXEC sp_OADestroy @DriveCollection
RETURN
END
새 기능을 사용할 수 있도록 "Ole 자동화 절차" 구성 설정을 활성화해야 합니다. 그렇지 않으면 실패합니다.
EXECUTE sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE
GO
EXECUTE sp_configure 'Ole Automation Procedures', 1
RECONFIGURE WITH OVERRIDE
GO
ufn_LogicalDiskDrives 테스트
SELECT * FROM [dbo].[ufn_LogicalDiskDrives]()
새 저장 프로시저 sp_fixeddrives는 모든 데이터베이스에서 호출할 수 있도록 시스템 개체로 표시됩니다. 이 저장 프로시저는 ufn_LogicalDiskDrives 함수에서 반환된 결과를 사용하고 총 데이터 파일 크기 및 총 로그 파일 크기 정보를 최종 결과에 추가합니다. 다음은 T-SQL 생성 코드입니다.
select ISNULL(DF.DriveLetter, LF.DriveLetter) DriveLetter, DF.DataSize_GB, LF.LogSize_GB from (
select left(physical_name, 1) DriveLetter, CAST(sum(size)/128.0/1024 AS DECIMAL(10,2)) DataSize_GB
from sys.master_files
where type_desc='ROWS'
group by left(physical_name, 1), type_desc
) DF
full outer join
(
select left(physical_name, 1) DriveLetter, CAST(sum(size)/128.0/1024 AS DECIMAL(10,2)) LogSize_GB
from sys.master_files
where type_desc='LOG'
group by left(physical_name, 1), type_desc
) LF
on DF.DriveLetter=LF.DriveLetter
결과는 다음과 같습니다.
마지막으로 저장 프로시저 sp_fixeddrives의 T-SQL 생성 코드는 다음과 같습니다.
USE [master]
GO
CREATE PROC dbo.sp_fixeddrives
WITH ENCRYPTION
AS
BEGIN
SET NOCOUNT ON
-----Reporting Drive details ....
select LDD.DriveLetter ,[VolumeName] ,[FileSystem]
,CAST(TotalSize/1024.0/1024/1024.0 AS DECIMAL(10,2)) [Capacity_GB]
,CAST(FreeSpace/1024.0/1024/1024.0 AS DECIMAL(10,2)) [FreeSpace_GB]
,CAST((CAST(FreeSpace/1024.0/1024/1024.0 AS DECIMAL(10,2))/CAST(TotalSize/1024.0/1024/1024.0 AS DECIMAL(10,2)) ) *100 AS DECIMAL(10,2)) [Free %]
, ISNULL( DBFiles.DataSize_GB,0) DataSize_GB,ISNULL( DBFiles.LogSize_GB,0) LogSize_GB
from master.[dbo].[ufn_LogicalDiskDrives]() LDD
full outer join
(
select ISNULL(DF.DriveLetter, LF.DriveLetter) DriveLetter, DF.DataSize_GB, LF.LogSize_GB from
(
select left(physical_name, 1) DriveLetter, CAST(sum(size)/128.0/1024 AS DECIMAL(10,2)) DataSize_GB
from sys.master_files
where type_desc='ROWS'
group by left(physical_name, 1), type_desc
) DF
full outer join
(
select left(physical_name, 1) DriveLetter, CAST(sum(size)/128.0/1024 AS DECIMAL(10,2)) LogSize_GB
from sys.master_files
where type_desc='LOG'
group by left(physical_name, 1), type_desc
) LF
on DF.DriveLetter=LF.DriveLetter
) DBFiles
ON DBFiles.DriveLetter =LDD.DriveLetter
SET NOCOUNT OFF
END
저장 프로시저를 시스템 개체로 표시합니다.
USE [master]
GO
EXEC sys.sp_MS_marksystemobject 'sp_fixeddrives'
다음은 코드를 실행한 후의 출력입니다.
