Solved

sql linked servers

Posted on 2010-08-26
2
291 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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Trigger for audit 26 71
Stored procedure query with if 27 37
How to enforce inte 8 43
How to place a condition in a filter criteria in t-sql (#2)? 10 42
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

867 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now