Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Database list from multiple SQL servers and instances??? Expand / Collapse
Author
Message
Posted Tuesday, December 11, 2012 2:29 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, September 12, 2014 8:43 AM
Points: 5, Visits: 69
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
Post #1394958
Posted Tuesday, December 11, 2012 2:42 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:58 AM
Points: 5,369, Visits: 9,919
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
Post #1394967
Posted Wednesday, December 12, 2012 12:11 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, March 3, 2014 4:05 AM
Points: 47, Visits: 236
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.

Post #1395459
Posted Wednesday, December 12, 2012 2:29 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, September 12, 2014 8:43 AM
Points: 5, Visits: 69
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
Post #1395521
Posted Wednesday, December 12, 2012 6:34 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, September 19, 2014 10:37 AM
Points: 139, Visits: 623
@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;"
)
Post #1395635
Posted Wednesday, December 12, 2012 6:37 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, September 19, 2014 10:37 AM
Points: 139, Visits: 623
<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
)
Post #1395639
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse