sql 获取链接服务器磁盘占用信息

可以将以下代码编写为SP，如果需要，可以加上获取时间（getDate()），插入表中.
该sql会调用xp_cmdshell查询磁盘总大小，一般服务器会因安全问题禁用该SP！！！
取硬盘可用空间可用系统过程实现：EXEC master.sys.xp_fixeddrives



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..#useddrives_temp') is not null drop table #useddrives_temp
if OBJECT_ID('tempdb..#totaldrives_temp') is not null drop table #totaldrives_temp
if OBJECT_ID('tempdb..#alldriveres_temp') is not null drop table #alldriveres_temp

create table tempdb..#useddrives_temp(drive char(1),freesize bigint)
create table tempdb..#totaldrives_temp(size varchar(100));
create table tempdb..#alldriveres_temp( server_name varchar(50),
drive char(1),
total_size_MB decimal(20,0),
used_size_MB decimal(20,0),
free_size_MB decimal(20,0),
used_precent decimal(5,2));
insert into  #totaldrives_temp exec xp_cmdshell 'wmic LogicalDisk get deviceid,size'
insert into  #useddrives_temp exec xp_fixeddrives

insert into #alldriveres_temp
select  'local',
f.drive,
        t.[totalsizeMB],
        (t.[totalsizeMB]-cast(f.freesize as decimal(20,0))),
        f.freesize,
cast(((t.[totalsizeMB]-cast(f.freesize as bigint))*100.0/t.[totalsizeMB]) as decimal(5,2))
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 #useddrives_temp f on t.drive=f.drive


-----获取链服服务器磁盘占用信息

declare @allLinkServer cursor
declare @serverNameDesc nvarchar(50)
declare @linkServer varchar(20)
declare @str1 nvarchar(2000)

set @allLinkServer=CURSOR for
SELECT distinct [name],[data_source]
  FROM [master].[sys].[servers]
  where is_linked =1

open @allLinkServer
fetch next from @allLinkServer into @linkServer,@serverNameDesc

while (@@FETCH_STATUS=0)
begin
truncate table #totaldrives_temp
truncate table #useddrives_temp

--set @str1='
               
--  select '''+Rtrim(@serverNameDesc)+''' ,*
--  from  OPENQUERY('+Rtrim(@linkServer)+',''SET FMTONLY OFF;SET  NOCOUNT ON  exec master.dbo.xp_fixeddrives'') a'

set @str1='
insert into  #totaldrives_temp select * from OPENQUERY('+Rtrim(@linkServer)+',''SET FMTONLY OFF;SET  NOCOUNT ON  exec master.dbo.xp_cmdshell ''wmic LogicalDisk get deviceid,size'' '') a
insert into  #useddrives_temp select * from OPENQUERY('+Rtrim(@linkServer)+',''SET FMTONLY OFF;SET  NOCOUNT ON  exec master.dbo.xp_fixeddrives'') b
insert into #alldriveres_temp
select  '''+Rtrim(@serverNameDesc)+''',
f.drive,
        t.[totalsizeMB],
        (t.[totalsizeMB]-cast(f.freesize as decimal(20,0))),
        f.freesize,
cast(((t.[totalsizeMB]-cast(f.freesize as bigint))*100.0/t.[totalsizeMB]) as decimal(5,2))
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 #useddrives_temp f on t.drive=f.drive '

begin try
exec (@str1)
end try
begin catch
print N'错误'
--insert into #fdtest2 values(@serverNameDesc,0,0)
end catch
fetch next from  @allLinkServer into @linkServer,@serverNameDesc
end
close @allLinkServer
deallocate @allLinkServer


select * from #alldriveres_temp