xp_cmdshell可以执行操作系统命令行，这段脚本用fsutil volume diskfree命令对26个字母的盘符遍历了一遍，不是很好，改用wmic会方便些，如下：
　　EXEC xp_cmdshell 'wmic logicaldisk get caption，freespace，size';
　　(2) 扩展存储过程xp_fixeddrives
--exec xp_fixeddrives
IF object_id('tempdb..#drivefreespace') IS NOT NULL
DROP TABLE #drivefreespace
CREATE TABLE #drivefreespace(Drive CHAR(1)， FreeMb bigint)
INSERT #drivefreespace EXEC ('exec xp_fixeddrives')
SELECT * FROM #drivefreespace
Drive
FreeMb
C
9316
D
180013
　　总算不依赖操作系统命令了，不过，这个存储过程只能返回磁盘可用空间，没有磁盘总空间。
　　(3) DMV/DMF: sys.dm_os_volume_stats
SELECT DISTINCT
@@SERVERNAME as [server]
，volume_mount_point as drive
，cast(available_bytes/ 1024.0 / 1024.0 / 1024.0 AS INT) as free_gb
，cast(total_bytes / 1024.0 / 1024.0 / 1024.0 AS INT) as total_gb
FROM sys.master_files AS f
CROSS APPLY sys.dm_os_volume_stats(f.database_id， f.file_id)
ORDER BY @@SERVERNAME， volume_mount_point
server
drive
free_gb
total_gb
…
C:
9
59
…
D:
175
237
　　从SQL Server 2008 R2 SP1开始，有了这个很好用的DMF: sys.dm_os_volume_stats，弥补了之前xp_fixeddrives没有磁盘总空间的不足。
　　不过，看它的参数可以知道，没被任何数据库使用的磁盘，是查看不了的，所以xp_fixeddrives还有存在的必要。
　　二. 数据库可用空间
　　1. 文件可用空间查看
　　(1) 文件已用空间，当前大小(已分配空间)，大值，如下：
select @@SERVERNAME as server_name
，DB_NAME() as database_name
，case when data_space_id = 0 then 'LOG'
else FILEGROUP_NAME(data_space_id)
end as file_group
，name as logical_name
，physical_name
，type_desc
，FILEPROPERTY(name，'SpaceUsed')/128.0 as used_size_Mb
，size/128.0 as allocated_size_mb
，case when max_size = -1 then max_size
else max_size/128.0
end as max_size_Mb
，growth
，is_percent_growth
from sys.database_files
where state_desc = 'ONLINE'
　　(2) 再算上磁盘的空闲空间，改动如下：
select @@SERVERNAME as server_name
，DB_NAME() as database_name
，case when data_space_id = 0 then 'LOG'
else FILEGROUP_NAME(data_space_id)
end as file_group
，name as logical_name
，physical_name
，type_desc
，FILEPROPERTY(name，'SpaceUsed')/128.0 as used_size_mb
，size/128.0 as allocated_size_mb
，case when max_size = -1 then max_size
else max_size/128.0
end as max_size_mb
，vs.available_bytes/1024.0/1024 as disk_free_mb
，growth
，CAST(is_percent_growth as int) as is_percent_growth
from sys.database_files df
cross apply sys.dm_os_volume_stats(DB_ID()，df.file_id) vs
where state_desc = 'ONLINE'
　　如果是SQL Server 2008 SP1以前的版本，可用xp_fixeddrives生成磁盘空闲空间表，再进行关联。
　　(3) 结合文件是否自增长，文件大值，磁盘空间，算出文件可用空间比率，改动如下：
select @@SERVERNAME as server_name
，DB_NAME() as database_name
，case when data_space_id = 0 then 'LOG'
else FILEGROUP_NAME(data_space_id)
end as file_group
，name as logical_name
，physical_name
，type_desc
，FILEPROPERTY(name，'SpaceUsed')/128.0 as used_size_mb
，size/128.0 as allocated_size_mb
，case when max_size = -1 then max_size
else max_size/128.0
end as max_size_mb
，vs.available_bytes/1024.0/1024 as disk_free_mb
，case when growth = 0 then  (size - FILEPROPERTY(name，'SpaceUsed'))*1.0/size
when growth > 0 and max_size = -1 then ((size/128.0 + vs.available_bytes/1024.0/1024) - FILEPROPERTY(name，'SpaceUsed')/128.0)/(size/128.0 + vs.available_bytes/1024.0/1024)
when growth > 0 and max_size <> -1 and (max_size/128.0 - vs.available_bytes/1024.0/1024) >= 0 then ((size/128.0 + vs.available_bytes/1024.0/1024) - FILEPROPERTY(name，'SpaceUsed')/128.0)/(size/128.0 + vs.available_bytes/1024.0/1024)
when growth > 0 and max_size <> -1 and (max_size/128.0 - vs.available_bytes/1024.0/1024) <  0 then (max_size - FILEPROPERTY(name，'SpaceUsed'))*1.0/max_size
else null
end as free_space_percent
，growth
，CAST(is_percent_growth as int) as is_percent_growth
from sys.database_files df
cross apply sys.dm_os_volume_stats(DB_ID()，df.file_id) vs
where state_desc = 'ONLINE'