• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 775
  • Last Modified:

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.
0
sifuhall
Asked:
sifuhall
1 Solution
 
sifuhallAuthor Commented:
Ooops, and by list I mean create a text list.
0
 
SoftEng007Commented:
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
0
 
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
begin
      select top 1 @id = id, @sql = cmd from #exec
      order by id
      exec (@sql)
      delete from #exec where id = @id
end

select * from #serverdbs

drop table #servers
drop table #serverdbs
drop table #exec
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now