-- Script to get all the info from the Linked Server properties
-- Also extract the ALIAS from the client network utility
-- Also extract the DNS IP from the Alias
use master
go

if exists (select * from tempdb..sysobjects where name = 'linkedsvr_detail')
drop table tempdb..linkedsvr_detail
if exists (select * from tempdb..sysobjects where name = 'linkedsvr_summary')
drop table tempdb..linkedsvr_summary
if exists (select * from tempdb..sysobjects where name = 'linkedsvr_dns')
drop table tempdb..linkedsvr_dns
go

-- SQL Client Network Alias
create table #alias(Alias nvarchar(50), Server nvarchar(250))
insert into #alias exec master..xp_regenumvalues 'HKEY_LOCAL_MACHINE','SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo'
update #alias set Server = substring(Server, charindex(',', Server)+1, 100)

select distinct @@servername FromServer, s.srvname LinkedServerName, a.server alias, '255.255.255.255' ip, isnull(a.server, datasource) rmtserver, u.rmtloginame
into tempdb..linkedsvr_summary
from sysservers s
join sysoledbusers u on s.srvid = rmtsrvid
left join #alias a on a.alias = s.datasource
where s.isremote = 1 and u.rmtloginame is not null
order by 2,3

select @@servername FromServer, s.srvname LinkedServerName, datasource, a.server alias, u.rmtloginame, l.loginname
into tempdb..linkedsvr_detail
from sysservers s
join sysoledbusers u on s.srvid = rmtsrvid
join syslogins l on u.loginsid = l.sid
left join #alias a on a.alias = s.datasource
where s.isremote = 1 
order by s.srvname, u.rmtloginame

-- DNS Alias Entry
create table #dns(alias nvarchar(50) primary key, ip varchar(15))

insert into #dns(alias)
select distinct case when charindex('\',rmtserver)>0 then left(rmtserver,charindex('\',rmtserver)-1) else rmtserver end 
from tempdb..linkedsvr_summary

create table #output(output nvarchar(255)) -- shell cmd output

declare @alias nvarchar(50), @cmd nvarchar(250), @ip varchar(15)
declare ALIASCUR cursor read_only fast_forward
for select alias from #dns

open ALIASCUR
fetch next from ALIASCUR into @alias
while @@fetch_status = 0
begin
set @cmd = 'PING -n 1 ' + @alias
insert into #output exec master..xp_cmdshell @cmd
select @ip = substring(output, charindex('[',output)+1,charindex(']',output)-1-charindex('[',output))
from #output where left(output,7)='Pinging'

update #dns set ip = isnull(@ip,'DNS error') where alias = @alias
fetch next from ALIASCUR into @alias
end
close ALIASCUR
deallocate ALIASCUR

update s
set ip = d.ip
from tempdb..linkedsvr_summary s
left join #dns d on case when charindex('\',rmtserver)>0 then left(rmtserver,charindex('\',rmtserver)-1) else rmtserver end = d.alias

select * into tempdb..linkedsvr_dns from #dns -- just for backups

drop table #dns
drop table #output
drop table #alias

-- Now you need to gather info from all servers and see if we have SA login used by Linked Servers

-- select * from tempdb..linkedsvr_summary
-- select * from tempdb..linkedsvr_detail
-- select * from tempdb..linkedsvr_dns
-- select @@servername, lower(name) from master..syslogins where sysadmin = 1

-- select fromserver, [to] LinkedServerName, l.Login
-- from dbo.LinkedServers s 
-- join sa_logins l on l.Server=s.toserver and l.Login=s.login
-- order by 1,2

This piece of code is really useful to manage the Linked Servers. It will tell you if a linked server is no longer valid. That is pointing to an invalid DNS entry or an address that doesn't ping.

Linked Servers Summary

SQL