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