Solved

sql linked servers

Posted on 2010-08-26
2
279 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
Comment Utility
this will help you move further

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

Assisted Solution

by:Marcjev
Marcjev earned 250 total points
Comment Utility
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

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
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.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

744 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

16 Experts available now in Live!

Get 1:1 Help Now