Search for All Connectable SQL Servers 1

[kjv1611]

Is there any way to search for all connectable SQL Servers in a query?

I know there are ways to look at all databases in a given server, and that sort of thing, but what about the various SQL servers? Where I work, we have multiple SQL Servers, and each of them have multiple databases. So it would be nice, at times, to be able to at least list out all the connectable SQL Servers. I suppose what'd be super cool is if I could somehow list out each SQL server, and then list out all databases under that server....

Or create a table somewhere that lists all connectable SQL servers, and all databases under each server. When I say connectable, I mean, that I am able to connect to with my current credentials.

Thanks for any thoughts, references, ideas..

 

[kjv1611]

My first guess on this is creating a loop that would loop through all the possible names for servers. To my knowledge, the server names are all the same, aside from a numeric 2 digit code. So shouldn't it be possible to use a variable or two for looping through the server names?

My other piece of the guess is to use error handling or CATCH and.. I forget the rest of that phrase... to get the results.

 

[SQLBill]

The trouble is that SQL Server servers are not connected to each other, so you can't find one server from another. That is why there is the Linked Server option. Using Linked Server to connect two or more SQL Server servers is the only way for one to see another.

If the naming convention is similar, for example each server with SQL Server on it has SQL in the name, and are on the same domain, you might be able to do a OS command to find all the servers.

But again, unless you have linked them, you aren't going to be able to query all of them from one SQL Server.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[kjv1611]

That's what I thought was the case, but thanks for clarifying. I wonder if I can build a tool in Access, perhaps, that'll use error handling or some other method for searching.. I may just ask over there for ideas after some more Googling.