List all servers and databases

Is there an easy way to list all servers and databases I have in Enterprise Manager?  I have about 50 servers and I don't know how many hundreds of databases.
Who is Participating?
Ken SelviaRetiredCommented:
This looks like it works OK to me but see comments for assumptions and cautions:

--Run in QA from any SQL server.

create table #servers (servername varchar(255))

create table #serverdbs (
      SERVERNAME varchar(255),
      DATABASE_NAME sysname,
      DATABASE_SIZE int,
      REMARKS varchar(255)

-- Use OSQL -L to list known servers. Requires xp_cmdshell permissions.
insert #servers
      exec master..xp_cmdshell 'osql -L'

-- Generate SQL statements to exec sp_databases against all known servers.
-- Note Trusted_connection=yes assumes the account SQL runs as on this machine has trusted login for all servers. Not likley so you will need to replace
-- this with User=sa;pwd=pwd. If there is no single login with permissions on all servers this will be more complicated

select id = identity(int,1,1), 'INSERT #serverdbs SELECT '''+ltrim(servername) +''' servername, DATABASE_NAME, DATABASE_SIZE, REMARKS FROM OPENROWSET(''SQLOLEDB'', ''Trusted_Connection=yes;Data Source='+ltrim(servername)+';'', ''SET FMTONLY OFF;EXEC master.dbo.sp_databases'')' cmd
into #exec
from #servers
where servername like '    %'

-- Debug to see what we have
select * from #exec

-- Delete servers you don't want to query

delete from #exec
where cmd not like '%SERVER1%' AND cmd not like '%SERVER2%'


-- Loop through serverlist and exec sp_databases on each. Store results in #serverdbs
declare @sql varchar(2000), @id int
while (select count(1) from #exec) > 0
      select top 1 @id = id, @sql = cmd from #exec
      order by id
      exec (@sql)
      delete from #exec where id = @id

select * from #serverdbs

drop table #servers
drop table #serverdbs
drop table #exec
sifuhallAuthor Commented:
Ooops, and by list I mean create a text list.
HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL Server\80\Tools\SQLEW\Registered Servers X

contains a list of all registered servers.

the databases are queried from master after connecting to a server in enterprise manager.
master..sp_databases lists each database on a server
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.