f T-SQL 查看windows 邏輯磁碟的可用空間 ~ 迪貝之家

T-SQL 查看windows 邏輯磁碟的可用空間

IF OBJECT_ID('tempdb..#G_DB_FILE_LIST') IS NOT NULL
    DROP TABLE #G_DB_FILE_LIST;
create table #G_DB_FILE_LIST (current_db varchar(100), physical_name varchar(300));

exec sp_MSforeachdb 'use [?];

begin
INSERT  #G_DB_FILE_LIST select db_name() , physical_name From  sys.database_files;
end'
DECLARE logic_disk_cursor CURSOR FORWARD_ONLY READ_ONLY FOR
SELECT  distinct current_db, substring(physical_name,1,1) as logic_disk FROM #G_DB_FILE_LIST;
DECLARE @logic_disk_free_size TABLE (logic_disk char, free_mb int, warning_flag char)
DECLARE @current_db varchar(100);
DECLARE @logic_disk char;
DECLARE @cnt int;
DECLARE @outputinfo varchar(MAX);
DECLARE @free_mb int;
set   @outputinfo = N'N';

Declare outputinfo_cursor   CURSOR FORWARD_ONLY READ_ONLY FOR
select logic_disk, free_mb from @logic_disk_free_size where warning_flag = '1';


insert @logic_disk_free_size(logic_disk , free_mb ) exec MASTER..xp_fixeddrives;

-- Open the cursor
OPEN logic_disk_cursor;
-- Retrieve one row at a time from the cursor
FETCH NEXT
FROM logic_disk_cursor
INTO @current_db,@logic_disk;

-- Process and retrieve new rows until no more are available
WHILE @@FETCH_STATUS = 0
  BEGIN
    select  @cnt = count(*) from  @logic_disk_free_size where logic_disk = @logic_disk and free_mb <= 20480;
   if @cnt >= 1
    update @logic_disk_free_size set warning_flag = '1' where logic_disk = @logic_disk;

   FETCH NEXT
   FROM logic_disk_cursor
   INTO @current_db,@logic_disk;
  END
-- Close the cursor
CLOSE logic_disk_cursor;
-- Deallocate the cursor
DEALLOCATE logic_disk_cursor;

OPEN outputinfo_cursor;

FETCH NEXT
FROM outputinfo_cursor
INTO @logic_disk,@free_mb;

WHILE @@FETCH_STATUS = 0
begin

set   @outputinfo =  @outputinfo + ':' + @logic_disk + '/'+ cast(@free_mb as varchar) ;

   FETCH NEXT
   FROM outputinfo_cursor
   INTO @logic_disk,@free_mb;
end

   -- Close the cursor
CLOSE outputinfo_cursor;
-- Deallocate the cursor
DEALLOCATE outputinfo_cursor;

if len(@outputinfo) = 1
   print N'Y';
else
   print  @outputinfo;
select * from   @logic_disk_free_size