Get list of linked servers and where they are used in database

By Allen Stoner

Here's a quick and dirty query that uses a cursor to loop across all the linked servers on a MS SQL server and check the current database for that string. Resulting output not formated real great, but it will point you in the right direction for finding linked servers.

declare @srvName as varchar(100)
declare @cnt as integer
declare svrCursor cursor for select srvname from master.dbo.sysservers
open svrCursor
fetch next from svrCursor into @srvName
while @@FETCH_STATUS = 0
begin
select @cnt = COUNT(*)
from sysobjects o join
  syscomments c on o.id = c.id
where c.text like '%' + @srvName + '%'

If @cnt > 0
select @srvName 'Server Name' ,o.name,c.text,*
from sysobjects o join
  syscomments c on o.id = c.id
where c.text like '%' + @srvName + '%'

fetch next from svrCursor into @srvName
end
close svrCursor
deallocate svrCursor

Get list of linked servers and where they are used in database  (559 Views)