扩展 xp_fixeddrives

 

xp_fixeddrvies提供磁盘分区的信息太少,如下的脚本提供磁盘分区的使用情况,运行需要管理员权限!
if not exists( select * from sys.configurations(nolock) cc 
 where cc.name='xp_cmdshell' 
 and cc.value_in_use=1)
 begin
    exec sp_configure 'show advanced options',1
    reconfigure

    exec sp_configure 'xp_cmdshell',1
    reconfigure
 end

if OBJECT_ID('tempdb..#fixeddrives_temp') is not null
drop table #fixeddrives_temp;
Go


if OBJECT_ID('tempdb..#totaldrives_temp') is not null
drop table #totaldrives_temp;
Go

create table tempdb..#fixeddrives_temp(drive char(1),freesize bigint)
create table tempdb..#totaldrives_temp(size varchar(100));
insert into  #totaldrives_temp exec xp_cmdshell 'wmic LogicalDisk get deviceid,size'
insert into  #fixeddrives_temp exec xp_fixeddrives

select    f.drive,
        t.[totalsizeMB] as [总空间MB],
        (t.[totalsizeMB]-cast(f.freesize as decimal(20,0))) as [已用空间MB],
        f.freesize as [未用空间MB],
cast(((t.[totalsizeMB]-cast(f.freesize as bigint))*100.0/t.[totalsizeMB]) as decimal(5,1)) as [已使用%],
cast((cast(f.freesize as bigint)*100.0/t.[totalsizeMB]) as decimal(5,1)) as [未使用%]
from 
(

select rtrim(left(LTRIM(size),1)) as drive,
cast(replace(replace(replace(ltrim(RIGHT(rtrim(size),LEN(size)-2)),char(9),''),char(10),''),CHAR(13),'') as bigint)/1024/1024 as [totalsizeMB]

from  #totaldrives_temp 
where size not like '%DeviceID  Size%' and LEN(size) not IN (0,1)
) AS t
inner join #fixeddrives_temp f
on t.drive=f.drive


 

转载于:https://www.cnblogs.com/fly_zj/archive/2012/05/06/2485876.html

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值