Solved

sql linked servers

Posted on 2010-08-26
2
308 Views
Last Modified: 2012-05-10
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.

0
Comment
Question by:LennyGray
2 Comments
 
LVL 16

Accepted Solution

by:
vdr1620 earned 250 total points
ID: 33535042
this will help you move further

http://support.microsoft.com/kb/203638
0
 
LVL 10

Assisted Solution

by:Marcjev
Marcjev earned 250 total points
ID: 33535147
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

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
T-SQL to Update Table Dynamically 2 55
How to query LOCK_ESCALATION 4 41
Present Absent from working date rage 11 35
SQL Server Configuration Manager WMI Error 11 16
If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

792 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question