[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

List all servers and databases

Posted on 2004-11-02
3
Medium Priority
?
771 Views
Last Modified: 2012-08-14
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
Comment
Question by:sifuhall
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 1

Author Comment

by:sifuhall
ID: 12473813
Ooops, and by list I mean create a text list.
0
 
LVL 9

Expert Comment

by:SoftEng007
ID: 12474202
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
 
LVL 12

Accepted Solution

by:
kselvia earned 2000 total points
ID: 12476587
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

650 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