Sql-server – How to join the updated result set of multiple servers

sql serversql-server-2008

I have got 4 different servers and I want to get the free diskspace of all those in a single output.
using xp_fixeddrives we will get the free space in each drive .

Its not possible to get into all servers and check it as above.So I am trying to execute all it a once.

for example the result set of one server is as follows 

drive   free_mb
C       1816
D       2553
E       2882
F       6247
G       16936
H       7281
I       2395

As I am a starter to sql please do help me to sort out this issue .

Best Answer

Something like this should do it:

create table #Tmp
(
  Drive char(1),
  MBFree int,
  ServerName varchar(50)
)

insert into #Tmp(Drive, MBFree) exec Server1.master.dbo.xp_fixeddrives
update #Tmp set ServerName = 'Server1' where ServerName is null

insert into #Tmp(Drive, MBFree) exec Server2.master.dbo.xp_fixeddrives
update #Tmp set ServerName = 'Server2' where ServerName is null

select *
from #Tmp
order by ServerName, Drive

drop table #Tmp

Result:

Drive MBFree      ServerName
----- ----------- --------------------------------------------------
C     57859       Server1
D     384636      Server1
F     82353       Server1
C     4963        Server2
E     10605       Server2

Server1 and Server2 has to be Linked Servers in the server where you are executing the above statements.

Related Question