Database list from multiple SQL servers and instances???

Question

Database list from multiple SQL servers and instances???

angelspawn

Old Hand

Points: 331
More actions
December 11, 2012 at 2:29 am

#286107

Hi
I'm currently looking for a script to get a list of all databases from our SQL 2005 and above instances.
Currently I have to pull this weekly from each instance alone but I don't know how to get this more automated.
Can anyone help me?
Thanks in advance and Kind Regards
angelspawn
I currently use the script from here[/url]

Viewing 6 posts - 1 through 5 (of 5 total)

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

[font="Courier New"]SELECT name FROM sys.databases[/font] will solve the problem you describe, although I can't help suspecting that your requirement is somewhat more complex than that. You can use a Central Management Server to run it on all servers at once, or use SSIS to loop through your servers one at a time, or you can set up each server as a linked server and run the query like that.

John

Hemant.R Ten Centuries Points: 1286 More actions · Answer 2

if you having sql server mgmt studio 2008,

you can registered no of instance using central server mgmt .

once registration done u can directly execute query on multiple instance.

PFA.

angelspawn Old Hand Points: 331 More actions · Answer 3

We have everything from SQL 2005 up to SQL 2008R2 SSMS.

I currently have a SQL 2005 server where i have all others added as linked server.

Thanks again for your ideas I will try them out

angelspawn

Your Name Here SSCrazy Points: 2344 More actions · Answer 4

@angelspawn

Part of the challenge to be a successful DBA is to use the tools at our disposal to answer these types of questions. There are [several] ways to answer your own question using batch files, PowerShell, SSMS, scripts, etc.

My batch file suggestion is only one possible way to accomplish this. I'll assume you have administrator access to all SQL Servers in your domain. Check [font="Courier New"]dblist.txt[/font] on completion.

[font="Courier New"]

@ECHO OFF

SQLCMD -Lc > serverlist.txt

FOR /F %%i IN (serverlist.txt) DO (

SQLCMD /S %%i /E /d master -o dblist.txt /Q"SET NOCOUNT ON SELECT SUBSTRING(@@SERVERNAME, 1, 30) AS ServerName, SUBSTRING([name],1,40) AS DBName FROM sysdatabases ORDER BY NAME SET NOCOUNT OFF;"

)

[/font]

Your Name Here SSCrazy Points: 2344 More actions · Answer 5

<LOL>

And my way is wrong! I [just] realized dblist.txt will be [overwritten] for each server. My bad. Here's a corrected batch file:

[font="Courier New"]

@ECHO OFF

SQLCMD -Lc > serverlist.txt

FOR /F %%i IN (serverlist.txt) DO (

SQLCMD /S %%i /E /d master /Q"SET NOCOUNT ON SELECT SUBSTRING(@@SERVERNAME, 1, 30) AS ServerName, SUBSTRING([name],1,40) AS DBName FROM sysdatabases ORDER BY NAME SET NOCOUNT OFF;" >>dblist.txt

)

[/font]