Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Database list from multiple SQL servers and instances???


Database list from multiple SQL servers and instances???

Author
Message
angelspawn
angelspawn
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 76
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
John Mitchell-245523
John Mitchell-245523
SSCertifiable
SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)

Group: General Forum Members
Points: 7476 Visits: 15142
SELECT name FROM sys.databases 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
Hemant.R
SSC Rookie
SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)

Group: General Forum Members
Points: 48 Visits: 240
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
angelspawn
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 76
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
Your Name Here
SSC-Enthusiastic
SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)

Group: General Forum Members
Points: 158 Visits: 792
@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 dblist.txt on completion.


@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;"
)

Your Name Here
Your Name Here
SSC-Enthusiastic
SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)

Group: General Forum Members
Points: 158 Visits: 792
<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:


@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
)

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search