Solved

List all servers and databases

Posted on 2004-11-02
739 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
Question by:sifuhall
    3 Comments
     
    LVL 1

    Author Comment

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

    Expert Comment

    by:SoftEng007
    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:
    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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
    Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
    Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
    Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

    931 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

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now