List of Dataabases from all the servers

  • Hi,

    We have 100+ servers and each servers contains few databases and i would like to get the all database list by each server/instance.

    Server1DB1
    DB2
    DB3
    Server2DB1
    DB2
    DB3
    DB4
  • One idea:

    Add every server to a group in Registered Servers in SSMS.  Then, open a query from that qroup which will open the query connected to every instance.

    You can then run a query to list all databases...

    In Tools | Options, go to Query Results | Multiserver Results and set 'Add server name to the results' to true and set 'Merge results' to true.  The results of the query will then list all servers and all databases in a single output.

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Personally, I'd put powershell to work. Make a list of your servers. Provide that list to Powershell. Run a query against every server to get the list of databases. Done. I don't have that exact piece of code available (although, do a web search, I'll bet it's out there), but I do have an old example of walking through servers running queries. Same idea, change the query. BTW, you don't necessarily need to enable remoting to make this work. Just connect to the servers from Powershell. Also, check out DBATools, a great way to make Powershell implementation easier.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • Jeffrey Williams wrote:

    One idea:

    Add every server to a group in Registered Servers in SSMS.  Then, open a query from that qroup which will open the query connected to every instance.

    You can then run a query to list all databases...

    In Tools | Options, go to Query Results | Multiserver Results and set 'Add server name to the results' to true and set 'Merge results' to true.  The results of the query will then list all servers and all databases in a single output.

    have you ever had to register 100 servers? I'd rather lose all my hair rather than pulling it out during the mind numbing registration process. (i'm bald due to tasks like that). I'm not sure management studio would even cope with that ... powershell for the win, but if you aren't comfortable with that then use excel

    build a quick bit of cheap and dirty VBA code that loops through your servers and runs "select @@servername,name from sysdatabases where id>4" - put it into the sheet and you have a solution

     

     

    MVDBA

  • Thanks Jeffery,  I will try it out.

  • Thanks Grant.

    I will try with Powershell as it should be little convenient.

  • MVDBA (Mike Vessey) wrote:

    Jeffrey Williams wrote:

    One idea:

    Add every server to a group in Registered Servers in SSMS.  Then, open a query from that qroup which will open the query connected to every instance.

    You can then run a query to list all databases...

    In Tools | Options, go to Query Results | Multiserver Results and set 'Add server name to the results' to true and set 'Merge results' to true.  The results of the query will then list all servers and all databases in a single output.

    have you ever had to register 100 servers? I'd rather lose all my hair rather than pulling it out during the mind numbing registration process. (i'm bald due to tasks like that). I'm not sure management studio would even cope with that ... powershell for the win, but if you aren't comfortable with that then use excel

    build a quick bit of cheap and dirty VBA code that loops through your servers and runs "select @@servername,name from sysdatabases where id>4" - put it into the sheet and you have a solution

     

    It's also pretty easy to generate this code that will add the registered servers using T-SQL:

    msdb.dbo.sp_sysmanagement_add_shared_registered_server
    @name = 'server name',
    @server_group_id = 1040,
    @server_name = 'server name',
    @description = N'',
    @server_type = 0
    @server_id : int OUTPUT

    Michael L John
    If you assassinate a DBA, would you pull a trigger?
    To properly post on a forum:
    http://www.sqlservercentral.com/articles/61537/

  • MVDBA (Mike Vessey) wrote:

    Jeffrey Williams wrote:

    One idea:

    Add every server to a group in Registered Servers in SSMS.  Then, open a query from that qroup which will open the query connected to every instance.

    You can then run a query to list all databases...

    In Tools | Options, go to Query Results | Multiserver Results and set 'Add server name to the results' to true and set 'Merge results' to true.  The results of the query will then list all servers and all databases in a single output.

    have you ever had to register 100 servers? I'd rather lose all my hair rather than pulling it out during the mind numbing registration process. (i'm bald due to tasks like that). I'm not sure management studio would even cope with that ... powershell for the win, but if you aren't comfortable with that then use excel

    build a quick bit of cheap and dirty VBA code that loops through your servers and runs "select @@servername,name from sysdatabases where id>4" - put it into the sheet and you have a solution

    Why would you do this manually?

    https://www.sqlservercentral.com/articles/managing-registered-servers-with-sql-powershell

    https://www.mssqltips.com/sqlservertip/3252/automate-registering-and-maintaining-servers-in-sql-server-management-studio-ssms/

    https://www.red-gate.com/simple-talk/sql/sql-tools/registered-servers-and-central-management-server-stores/

    There are plenty of other examples...

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • these are super cool examples

    I used to use C# and the Redgate SDK for things like this (such as checking if all 100 servers had the same database structure for 10 databases)

    While powershell is a nice re-usable scripting tool, and better than excel... I would say that excel (the hatred thing that it is)  would have the results on screen ready to save in any format you like by just re-running the macro/function.

    maybe not as automatable as powershell, but then not every solution is perfect

    MVDBA

Viewing 9 posts - 1 through 9 (of 9 total)

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