TSQL方法获取服务器磁盘空间信息

   日常运维中, 无需登录服务器Windows, 用TSQL方法即可获取服务器磁盘空间信息.

create table dbo.DBA_ServerDiskSpaceDetail
(LogID int not null identity(1,1),
 Drive nvarchar(50),             -- 磁盘盘符
 TotalSize decimal(10,2),     -- 总空间大小  GB
 FreeSize decimal(10,2),     -- 剩余空间大小  GB
 UsedSize decimal(10,2),    -- 已用空间大小  GB
 ADate  datetime
 constraint pk_DBA_ServerDiskSpaceDetail primary key(LogID)
)

-- 获取服务器磁盘空间信息
alter proc dbo.DBA_GetServerDiskSizeInfo
as
begin
  set nocount on

 if not exists(select 1 from sys.configurations 
                     where name=N'show advanced options' and value=1 and value_in_use=1)
 begin
   exec sys.sp_configure 'show advanced options', 1
   reconfigure
 end

 if not exists(select 1 from sys.configurations 
                    where name=N'xp_cmdshell' and value=1 and value_in_use=1)
 begin
   exec sys.sp_configure 'xp_cmdshell', 1
   reconfigure
 end


if object_id('tempdb..#result') is not null
   drop table #result

create table #result
(DeviceID nvarchar(10),
 DriveType nvarchar(10),
 FileSystem nvarchar(10),
 Freespace nvarchar(100),
 Size nvarchar(100))

if object_id('tempdb..#temp') is not null
   drop table #temp

create table #temp(rid int identity(1,1) not null,x nvarchar(4000))

insert into #temp (x)
   exec sys.xp_cmdshell  N'wmic LOGICALDISK get DeviceID,DriveType,FileSystem,Freespace,Size '

delete #temp where rid=1 or x is null  or ascii(left(x,1))=13

update #temp set x=replace(x,char(10),'')
update #temp set x=replace(x,char(13),'')
update #temp set x=replace(x,char(58),'')
update #temp set x=rtrim(x)

while exists(select 1 from #temp where charindex('  ',x,1)>0)
begin
   update #temp set x=replace(x,'  ',' ') where charindex('  ',x,1)>0
end


declare @tsql nvarchar(4000),
              @row nvarchar(4000),
              @colqty int

declare ap scroll cursor for
  select x from #temp order by rid

open ap
fetch first from ap into @row
while(@@fetch_status<>-1)
begin
   select @tsql=@row,   
              @colqty=0

   while(charindex(' ',@tsql,1)>0)
   begin
     select @colqty=@colqty+1,
                @tsql=stuff(@tsql,charindex(' ',@tsql,1),1,''',''')
   end

   if @colqty=4
   begin
      select @tsql='insert into #result(DeviceID,DriveType,FileSystem,Freespace,Size) select '''+@tsql+''' '
      exec(@tsql)
   end

   fetch next from ap into @row
end

close ap
deallocate ap

insert into dbo.DBA_ServerDiskSpaceDetail(Drive,TotalSize,FreeSize,UsedSize,ADate)
   select DeviceID,
              cast(cast(Size as bigint)/1024.0/1024.0/1024.0 as decimal(10,2)),
              cast(cast(Freespace as bigint)/1024.0/1024.0/1024.0 as decimal(10,2)),
              cast((cast(Size as bigint)-cast(Freespace as bigint))/1024.0/1024.0/1024.0 as decimal(10,2)),
              getdate()
   from #result
end