February 26, 2020 at 10:08 pm
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
February 26, 2020 at 10:58 pm
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
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
February 27, 2020 at 1:31 pm
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 27, 2020 at 4:28 pm
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
February 27, 2020 at 4:52 pm
Thanks Jeffery, I will try it out.
February 27, 2020 at 4:53 pm
Thanks Grant.
I will try with Powershell as it should be little convenient.
February 27, 2020 at 5:53 pm
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/
February 27, 2020 at 6:44 pm
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
There are plenty of other examples...
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
February 28, 2020 at 9:25 am
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 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply