sql linked servers

I have a sqlserver that contains six Linked Servers in its Server Objects.

I would like a stored procedure that would loop through all of the Linked Servers, listing their names, their Catalogs within each Linked Server and theirTables and Views.

LVL 10
LennyGrayAsked:
Who is Participating?
 
vdr1620Connect With a Mentor Commented:
this will help you move further

http://support.microsoft.com/kb/203638
0
 
MarcjevConnect With a Mentor Commented:
use master
select * from sys.servers where is_linked = 1

This will give you a list of all linked servers. Use this data to construct a dynamic statement per server querying for all databases or catalogs.
Use a statement like select name from [founddbname].master.sys.databases to get this list.

This information you will need to create new dynamic statement of the kind 'select * from [servername].[databasename].sys.colums

Below is level one of this query you need.
use master
declare @statement varchar(500)
declare @name varchar(50)
declare curs cursor for select data_source from sys.servers where is_Linked = 1
open curs
fetch next from curs into @name
while @@fetch_status = 0
begin
   set @statement = 'SELECT * from [' + @name +'].master.sys.databases '
   EXEC(@statement)
end

Open in new window

0
All Courses

From novice to tech pro — start learning today.