Create a list of DB names/multiple SQL servers from a single server

Question

Create a list of DB names/multiple SQL servers from a single server

Stephen Chen

SSC Rookie

Points: 36
More actions
March 6, 2007 at 10:14 am

#177108

How do I do, from a single SQL server, to create a list of the names of all active database/SQL server without using the linked server technique?
For instance, I have 10 production SQL servers, and 10 test/dev servers. Could I come up with a list of all production database/server names and another list of all test database/server names?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

i have this procedure that i use; sorry i don't know the source of where i got it: this lists all servers on the network, you'd then need to query each server for the db's it hosts...assuming you have a login that works on every server.

CREATE PROCEDURE dbo.ListLocalServers

AS

BEGIN

SET NOCOUNT ON

CREATE TABLE #servers(sname VARCHAR(255))

INSERT #servers EXEC master..XP_CMDShell 'OSQL -L'

-- play with ISQL -L too, results differ slightly

DELETE #servers WHERE sname='Servers:'

SELECT LTRIM(sname) FROM #servers WHERE sname != 'NULL'

DROP TABLE #servers

END

EXEC ListLocalServers

results:

(local)

ASTRAH

AURORA

BELLE

etc....

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Stephen Chen SSC Rookie Points: 36 More actions · Answer 2

Thanks.

Basically it is the list of DB names within the entire network SQL servers I am looking for. Ideally I'd like to see the possible [one-shot] way of getting the DB listing of the servers within a given network/domain.

Moreno-84941 Hall of Fame Points: 3258 More actions · Answer 3

Once you have the server name it should be possible to query the sysdatabases DB and get the DB-List for each server